Re: Seeking help with a query that takes too long

On Fri, 14 Nov 2003 11:00:38 -0500, "Nick Fankhauser"
<nickf@ontko.com> wrote:
>Good question... I've never used clustering in PostgreSQL before, so I'm
>unsure. I presume this is like clustering in Oracle where the table is
>ordered to match the index?

Yes, something like that.  With the exception that Postgres looses the
clustered status, while you INSERT and UPDATE tuples.  So you have to
re-CLUSTER from time to time.  Look at pg_stats.correlation to see, if
its necessary.

> Is there a way to flush out
>the cache in a testing situation like this in order to start from a
>consistent base?

To flush Postgres shared buffers:
    SELECT count(*) FROM another_large_table;

To flush your database pages from the OS cache:
    tar cf /dev/null /some/large/directory

And run each of your tests at least twice to get a feeling how caching
affects your specific queries.

Servus
 Manfred