Re: Performance question

On Wed, 12 Sep 2001, Tille, Andreas wrote:

> On Tue, 11 Sep 2001, Stephan Szabo wrote:
>
> > Well, the index isn't used because it estimates (apparently
> > correctly) that not using it is cheaper.  Because the information about
> > whether a row is valid is kept in the heap, for each index hit, the
> > heap needs to be read to see if the row is visible.  This results in
> > jumping about the heap file with seeks and such plus the index
> > search itself.  When most of the rows are going to be returned, the
> > sequence scan will generally be cheaper.
> It would me drive crazy if this "cheaper" solution would last miore than
> 30 seconds for PostgreSQL if MS-SQL server solves this tasks in less
> than a second.  This would really destroy all my plans with PostgreSQL
> if it would be true (what I can�t really imagine).
>
> > Alot of the real time may be being spent in the sort step.  You may want
> > to raise the amount of memory used for sorting and see if that helps.
> How to raise this memory amount.
Probably the best way is sort_mem in postgresql.conf in your data
directory and restarting the server.  It may take multiple iterations
to find a good number (and you may need to up your systems shared memory
limits if you run into it, but that differs from system to system).
For real world testing, you probably also want to raise the shared_buffers
value as well.

> This is more or less a theoretical question because sorting of about
> 150 items can�t last 30s.  The result set is:
It's sorting *before* doing the grouping, so it's the 170000 (or
whatever) rows being sorted by the group by columns.  If I guess
correctly (without looking at the code), the group by does a one pass
across the sorted rows breaking the groups when it sees the grouped
column values change, so it wants the rows in sorted order.