Re: [SQL] Re: pgsql-sql-digest V1 #225

"Steven M. Wheeler" <swheeler@sabre.com> writes:
> First my configuration:

Er, what version of Postgres?

> Using psql in interactive mode, I issue "select count(*) from
> currnt;".  The postmaster starts to read data in, as seen in vmstat by
> the bi stat jumping sharply.  Very rapidly, CPU goes to 0% idle,
> postmaster is using 99.4%, bi stat runs about 12K for 60-80 seconds,
> and then goes to 0, and everything stays there.  I never get a return
> and the system stays maxed.  When the row count in this table was
> below 2M, I would get a return count.

This is odd, all right.  Can you attach to the backend with a debugger
and see where it is (get a backtrace)?  It sounds like the backend's
going into an infinite loop, but I've never seen that sort of behavior
on such a simple query...

> Running psql in interactive mode, I issue "select * from currnt where
> cast(statdate as text) like '03-%-1999);".  This runs for quite
> awhile, eats into swap to the tune of 670MB+ and then bombs out with a
> palloc error.

This is a known problem that I hope to see fixed in 6.6, but it will
not be fixed for 6.5.   An expression involving any datatype more
complex than integers consumes temporary memory for each evaluation,
and currently the backend doesn't try to recover that memory until
end of statement.  So if you process enough tuples in one statement,
you run out of memory :-(.  We know how to fix this but it's too large
a change to make at this late stage of the 6.5 release cycle.

It sounds like this expression is consuming several hundred bytes per
iteration, which is more than I would've expected --- a couple dozen
bytes for the temporary text value should've been enough.  It could be
that the "like" operator itself is wasting memory internally, which
might be fixable now; I'll look into it.

In the meantime, you might be able to work around the problem by using
a less memory-hungry form of the WHERE expression --- for example, the
above query could probably be written without any text temporary asstatdate >= '03-01-1999' AND statdate <=
'03-31-1999';
This way only requires boolean intermediate values, which don't require
extra memory to be allocated.  (Haven't actually tried it, but I think
it should work.)

> Am I trying to run too large a DB?

No; there are people running Postgres DBs with individual tables larger
than 2Gb without trouble.  But it does depend on working around some of
the known limitations :-(.  The developers plan to address these
limitations in future releases, but there are only so many hours in the
day...
        regards, tom lane