On Fri, Mar 22, 2002 at 01:11:11AM +0000, Nigel J. Andrews wrote:
> Good point, I hadn't tried it since the upgrade becuase that wasn't why I
> upgraded (don't worry I've got a _long_ post on that subject waiting to be
> sent), I tightened up limits for the generation of the SQL string in the
> application before then. However, I have just tried it with 7 poster_names
> listed and top never reported even 8MB for the postgres footprint. I won't give
> the EXPLAIN output because it's not interesting and it would almost be an
> overlap with the contents of my long, pending post.
Hmm, with something that matches most of the table, it would likely choose a
sequential scan which can take quite a while over a large table.
> FWIW, the table has >1 million rows and the list of names I just gave the query
> includes some of the highest volume posters, including the top one with 55,000
> rows in the table. There is an index on the poster_name and one on the time
> columns.
If you're selecting on one column (poster_name) and sorting on another
(time) it may help to have an index on both (time,poster_name) since that
avoids the sort step. (I hope 7.2 estimates sort costs better than earlier
versions).
However, if you really want the whole output (rather than say the first 100
lines) and that really involves trawling a majority of the table, then you
are simply bound by disk transfer speed.
Alternativly, using a cursor may allow you to start outputting data before
the query has finished.
> Thanks for the comments, I didn't even know about the 6.5 memory leak.
Earlier versions of postgres tended to leak a lot within queries (not
between queries). 7.0 fixed most of them but still some issues with
functions and I don't beleive it's a problem anymore.
HTH,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Ignorance continues to thrive when intelligent people choose to do
> nothing. Speaking out against censorship and ignorance is the imperative
> of all intelligent people.
