MAX Query length

"Ansley, Michael" <Michael.Ansley@intec.co.za> writes:

Trawling through the code last night I noticed that:
#define MAX_QUERY_SIZE (BLCKSZ * 2)

Is there any conceivable reason why the query length would be dependent on
the block size?

Sure: you want to be able to INSERT a tuple of maximum size. In the
absence of dynamically sized text buffers, a reasonable estimate of
the longest INSERT command of interest is going to depend on BLCKSZ.

I don't know how long that particular constant has been defined like
that, though. I had the idea that it was the same as BLCKSZ, not 2x.
You may well find that frontend libpq is using a different value for
its buffer sizes than the backend is :-(

regards, tom lane

[Charset iso-8859-1 unsupported, filtering to ASCII...]

Trawling through the code last night I noticed that:
#define MAX_QUERY_SIZE (BLCKSZ * 2)

Is there any conceivable reason why the query length would be dependent on
the block size? Or do I just have old source code?

No great reason, but is seems like a good maximum. This controls the
buffer size on the client and server. Do you need it larger?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thanks for all the answers, everybody. Bruce, I had thought to start work
adjusting this so that the size wasn't limited at all. I'm just busy
gathering as much info as I can about the subject area, and hopefully in a
couple of days, if not earlier, I'll be in a position to start working on
the code.
I seem to remember there being a hackers guide somewhere. If I remember
right, it dealt with issues like where to check out the latest source from
cvs, rough standards, and other basic advice. Can anybody point me to it?

Thanks

MikeA

I was just thinking of removing the limit completely. The query would fail
when it could allocate more memory for the query string.

MikeA

Tom Lane wrote:

"Ansley, Michael" <Michael.Ansley@intec.co.za> writes:

Trawling through the code last night I noticed that:
#define MAX_QUERY_SIZE (BLCKSZ * 2)

Sure: you want to be able to INSERT a tuple of maximum size. In the
absence of dynamically sized text buffers, a reasonable estimate of
the longest INSERT command of interest is going to depend on BLCKSZ.

...

regards, tom lane

While I agree that it is reasonable that the query size should be
dependent on the block-size, there is an assumption here that the
type_in() and type_out() routines that do not expand the size of the
ascii representation of the tuple data in the query string to more than
twice is size in it's internal disk representation. An important
exception to this assumption would be large arrays of floating point
data that are stored with limited precision. A (single-precision) float
takes 4 bytes of space in a disk block, yet the ascii representation
for the same data before conversion could easily take in excess of 16
bits if it comes from a
piece of code like

double x;
int buf_pos
....
....
buf_pos +=
snprintf( &query_buf[buf_pos], (l_buf - buf_pos ), "%e", x);

somewhere in a front end. Perhaps it would be a good idea to increase
the multiplier in

#define MAX_QUERY_SIZE (BLCKSZ * 2)

to something larger than 2.

Bernie Frankpitt

Bernard Frankpitt <frankpit@pop.dn.net> writes:

Tom Lane wrote:

Sure: you want to be able to INSERT a tuple of maximum size. In the
absence of dynamically sized text buffers, a reasonable estimate of
the longest INSERT command of interest is going to depend on BLCKSZ.

Perhaps it would be a good idea to increase
the multiplier in
#define MAX_QUERY_SIZE (BLCKSZ * 2)
to something larger than 2.

This entire chain of logic will fall to the ground anyway once we support
tuples larger than a disk block, which I believe is going to happen
before too much longer. So, rather than argue about what the multiplier
ought to be, I think it's more productive to just press on with making
the query buffers dynamically resizable...

regards, tom lane

[Charset iso-8859-1 unsupported, filtering to ASCII...]

Thanks for all the answers, everybody. Bruce, I had thought to start work
adjusting this so that the size wasn't limited at all. I'm just busy
gathering as much info as I can about the subject area, and hopefully in a
couple of days, if not earlier, I'll be in a position to start working on
the code.
I seem to remember there being a hackers guide somewhere. If I remember
right, it dealt with issues like where to check out the latest source from
cvs, rough standards, and other basic advice. Can anybody point me to it?

Info Central/Documenation, see the Developers section.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Nice parody.

Tom Lane wrote:

Bernard Frankpitt <frankpit@pop.dn.net> writes:

Tom Lane wrote:

Sure: you want to be able to INSERT a tuple of maximum size. In the
absence of dynamically sized text buffers, a reasonable estimate of
the longest INSERT command of interest is going to depend on BLCKSZ.

Perhaps it would be a good idea to increase
the multiplier in
#define MAX_QUERY_SIZE (BLCKSZ * 2)
to something larger than 2.

This entire chain of logic will fall to the ground anyway once we support
tuples larger than a disk block, which I believe is going to happen
before too much longer. So, rather than argue about what the multiplier
ought to be, I think it's more productive to just press on with making
the query buffers dynamically resizable...

Yes, even if we choose to make some other limit (like Vadim
suggested around 64K), a query operating on them could be
much bigger. I already had some progress with a data type
that uses a simple, byte oriented lz compression buffer as
internal representation.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #

Well, I'm starting on this, so hopefully in a couple of weeks the length
limit of the query buffer will fade into insignificance.
Is somebody actively working on removing the tuple-length dependence on the
block size?

At present, disk blocks are set to 8k. Is it as easy as just adjusting the
constant to enlarge this? Testing queries larger than 16k with only an 8k
tuple size could be challenging.

MikeA

"Ansley, Michael" <Michael.Ansley@intec.co.za> writes:

At present, disk blocks are set to 8k. Is it as easy as just adjusting the
constant to enlarge this? Testing queries larger than 16k with only an 8k
tuple size could be challenging.

As of 6.5, it's just a matter of adjusting BLCKSZ in include/config.h,
rebuilding, and re-initdb-ing. The workable sizes are 8k 16k and 32k;
bigger than 32k fails for reasons I don't recall exactly (offsets
stored in signed shorts somewhere, no doubt).

Is somebody actively working on removing the tuple-length dependence on the
block size?

There was considerable discussion about it a few weeks ago, but I didn't
hear anyone actually committing to do the work :-(. Maybe when you've
made some progress on the text-length issues, someone will get excited
about the tuple-length issue...

regards, tom lane

Once I have recompiled with a new block size, how do I update the databases
that I already have. If I understand right, once the block size has been
updated, my current dbs will not work. Do I just pg_dump before make
install and then recreate the dbs and load the dumps afterwards?

As of 6.5, it's just a matter of adjusting BLCKSZ in
include/config.h,
rebuilding, and re-initdb-ing. The workable sizes are 8k
16k and 32k;
bigger than 32k fails for reasons I don't recall exactly (offsets
stored in signed shorts somewhere, no doubt).

MikeA

I wrote:

"Ansley, Michael" <Michael.Ansley@intec.co.za> writes:

At present, disk blocks are set to 8k. Is it as easy as just adjusting the
constant to enlarge this? Testing queries larger than 16k with only an 8k
tuple size could be challenging.

As of 6.5, it's just a matter of adjusting BLCKSZ in include/config.h,
rebuilding, and re-initdb-ing.

A further thought on this: if you increase BLCKSZ then at least some of
the fixed-size text buffers will get bigger, so it's not clear that you
will be stressing things all that hard if you take that route. Might be
easier to leave BLCKSZ alone and test with queries that are long and
complicated but don't actually require a large tuple size. Some
examples:

1. SELECT a,a,a,a,... FROM table;

2. SELECT a FROM table WHERE x = 1 OR x = 2 OR x = 3 OR ...;

3. Hugely complex CREATE TABLE commands (lots of constraints and
defaults and indexes, which don't enlarge the size of an actual
tuple of the table).

regards, tom lane

"Ansley, Michael" <Michael.Ansley@intec.co.za> writes:

Once I have recompiled with a new block size, how do I update the databases
that I already have. If I understand right, once the block size has been
updated, my current dbs will not work. Do I just pg_dump before make
install and then recreate the dbs and load the dumps afterwards?

Right, the real sequence when you are changing disk layout details is
pg_dumpall with old pg_dump and backend.
stop postmaster
rm -rf installation
make install
initdb
start postmaster
psql <pgdumpscript.

You may want to do your development work in a "playpen" installation
instead of risking breaking your "production" installation with these
sorts of shenanigans. I do that all the time here; for one thing I
don't have to bother saving and restoring any data when I blow away
a playpen installation.

The easiest kind of playpen setup is a separate server machine, but if
you only have one machine available then you do something like this to
build a playpen:

configure --with-pgport=5440 --prefix=/users/postgres/testversion

(Adjust playpen's port and install location to taste; make more than one
if you want...) BTW, if you are messing with the backend then your
playpen should also be built with --enable-cassert.

As I commented a moment ago, it's probably not really necessary for you
to change BLCKSZ for your testing, but the above tips are worth
repeating every so often for the benefit of new hackers.

regards, tom lane

Hi,
I've posted 3 messages to pgsql-general about a weird index problem I'm
having. I've found a very simple case that exhibits this problems.
This time I'm using a different database and different table that the
first 3 messages(It's the same pg install however).

The index called mcrl1_partnumber_index is an index on the 'reference'
field. The table was just vacuumed(with and without analyze).
The pg install is from CVS last night around 7pm Central time.

The problems seems to be rooted in 'OR' combined with 'LIKE'. If I remove
the % in the string, explain shows the same (high) cost. If I also remove
the 'LIKE' the cost basically goes to nothing. The cost is indeed
correct, either of the 2 first cases takes ~5 minutes, while the last one
(no LIKE) finishes instantly.

The weird thing is, why is the cost being calculated as being that high
when it's actually using the index on that field and is there a reason why
explain shows the index name twice?

I ran the same exact query on a MS SQL server with the same data, and
that took in comparison about 2 seconds to finish.
Both Postgres and MS SQL are on Pentium 100 servers(Yes, very pathetic),
and Linux 2.2.6 and NT 4.0 respectively.

Thanks,
Ole Gjerde

Here's the SQL:
---------------------
select * from mcrl1 where reference = 'AN914' OR reference LIKE 'AN914-%';

Here's the explain:
-----------------
mcrl=> explain select * from mcrl1 where reference = 'AN914' OR reference
LIKE AN914-%';
NOTICE: QUERY PLAN:

Index Scan using mcrl1_reference_index, mcrl1_reference_index on mcrl1
(cost=418431.81 rows=1 width=120)

EXPLAIN

Here's the table layout: 
------------
Table    = mcrl1
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |Length|
+----------------------------------+----------------------------------+-------+
| reference                        | varchar()                        |32 |
| cage_num                         | char()                           |5 |
| fsc                              | char()                           |4 |
| niin                             | char()                           |9 |
| isc                              | char()                           |1 |
| rnvc                             | char()                           |1 |
| rncc                             | char()                           |1 |
| sadc                             | char()                           |1 |
| da                               | char()                           |1 |
| description                      | varchar()                        |32 |
+----------------------------------+----------------------------------+-------+
Index:    mcrl1_partnumber_index

Ole Gjerde <gjerde@icebox.org> writes:

The pg install is from CVS last night around 7pm Central time.

Do you have USE_LOCALE defined?

The problems seems to be rooted in 'OR' combined with 'LIKE'. If I remove
the % in the string, explain shows the same (high) cost. If I also remove
the 'LIKE' the cost basically goes to nothing. The cost is indeed
correct, either of the 2 first cases takes ~5 minutes, while the last one
(no LIKE) finishes instantly.

When you have just "where reference = 'AN914'", the system knows it can
use the index to scan just the tuples with keys between AN914 and AN914
(duh). Very few tuples actually get fetched.

As soon as you use LIKE with a %, more tuples have to be scanned. It's
particularly bad if you have USE_LOCALE; with the current code, that
basically means that LIKE 'AN914-%' will cause all tuples beginning with
key AN914- and running to the end of the table to be scanned.

See the extensive thread on this topic from about a month or two back
in the pgsql-hackers mail list archives; I don't feel like repeating the
info now.

When you throw in the OR, the indexqual logic basically breaks down
completely; I think you end up scanning the entire table. (This could
be made smarter, perhaps, but right now I don't believe the system is
able to figure out the union of indexqual conditions.) I would say it
is an optimizer bug that it is not reverting to sequential scan here
... that would be a good bit faster, I bet.

regards, tom lane

On Thu, 15 Jul 1999, Tom Lane wrote:

Do you have USE_LOCALE defined?

Nope.. Not unless it defaults to on... I did a
./configure --prefix=/home/postgres ; make ; make install as usual

As soon as you use LIKE with a %, more tuples have to be scanned. It's
particularly bad if you have USE_LOCALE; with the current code, that
basically means that LIKE 'AN914-%' will cause all tuples beginning with
key AN914- and running to the end of the table to be scanned.

Ok.. I get that.. But why does LIKE 'AN914' have the same problem? The %
doesn't have to be there as long as it's either LIKE or ~*(or ~ etc)
query. And that still doesn't explain why it happens with USE_LOCALE
off..
Also, since the ='s work using OR, why wouldn't LIKE also? Both methods
would use the indexes, and the LIKE doesn't take that much longer to run..
Doesn't make sense, especially concerning what you mention below..

See the extensive thread on this topic from about a month or two back
in the pgsql-hackers mail list archives; I don't feel like repeating the
info now.

I haven't been able to find a discussion on this topic last few months, I
found discussion about something similar in March, but that didn't explain
it very well.. I'll just have to look some more :)

When you throw in the OR, the indexqual logic basically breaks down
completely; I think you end up scanning the entire table. (This could
be made smarter, perhaps, but right now I don't believe the system is
able to figure out the union of indexqual conditions.) I would say it
is an optimizer bug that it is not reverting to sequential scan here
... that would be a good bit faster, I bet.

Ok.. I can believe that.. This is a pretty nasty problem tho.. I don't
believe using OR with LIKE is all that rare.. Maybe it's rare on a 17
mill row table, but still..
What would be the outlook on fixing the problem and not the symptom? :)

As far as sequential scan being faster.. Unfortunately, this table has
about 17 million rows, so any kind of seq scan is gonna be really slow.

Thanks,
Ole Gjerde

Thanks, Tom.

Right, the real sequence when you are changing disk layout details is
pg_dumpall with old pg_dump and backend.
stop postmaster
rm -rf installation
make install
initdb
start postmaster
psql <pgdumpscript.

You may want to do your development work in a "playpen" installation
instead of risking breaking your "production" installation with these

etc., etc.

Ole Gjerde <gjerde@icebox.org> writes:

Ok.. I get that.. But why does LIKE 'AN914' have the same problem? The %
doesn't have to be there as long as it's either LIKE or ~*(or ~ etc)
query.

A pure "where field LIKE constant" doesn't have the problem; it's the
OR that does it. More specifically it's an OR of ANDs that doesn't work
very well.

By the time the parser gets done with it, your query looks like

select * from mcrl1 where
reference = 'AN914' OR
(reference LIKE 'AN914-%'
AND reference >= 'AN914-'
AND reference <= 'AN914-\377');

(ugly, ain't it?) Those comparison clauses are what need to be pulled
out and fed to the indexscan mechanism, so that only part of the table
gets scanned, not the whole table. Indexscan doesn't know anything
about LIKE, but it does grok >= and <=.

Unfortunately the current optimizer doesn't do it right. I looked into
a very similar bug report from Hiroshi Inoue (see his message of 3/19/99
and my response of 4/3 in the hackers archives), and what I found was
that the cause is a fairly fundamental optimizer design choice. The
ANDed conditions get split into separate top-level clauses and there's
no easy way to put them back together. The optimizer ends up passing
only one of them to the indexscan executor. That's better than nothing,
but on average you still end up scanning half the table rather than
just a small range of it.

I haven't been able to find a discussion on this topic last few months, I
found discussion about something similar in March, but that didn't explain
it very well.. I'll just have to look some more :)

I was referring to the discussion around 4/15/99 about why LIKE needs a
smarter way to generate the upper comparison clause. That's not
directly your problem, but it is causing the same kind of slowdown for
everyone who does use LOCALE...

When you throw in the OR, the indexqual logic basically breaks down
completely; I think you end up scanning the entire table. (This could
be made smarter, perhaps, but right now I don't believe the system is
able to figure out the union of indexqual conditions.)

I was wrong about that --- the executor *does* handle OR'd indexqual
conditions, basically by performing a new indexscan for each OR'd
condition. (That's why EXPLAIN is listing the index multiple times.)
The trouble with OR-of-ANDs is entirely the optimizer's fault; the
executor would do them fine if the optimizer would only hand them over
in that form.

What would be the outlook on fixing the problem and not the symptom? :)

I plan to look into fixing this for 6.6, but don't hold your breath
waiting...

regards, tom lane