On 20 Oct 2004 at 15:36, Josh Close wrote:
> On Wed, 20 Oct 2004 20:49:54 +0100, Gary Doades <gpd@gpdnet.co.uk> wrote:
> > Is this the select(1) query? Please post an explain analyze for this and any other "slow"
> > queries.
>
> I think it took so long 'cause it wasn't cached. The second time I ran
> it, it took less than a second. How you can tell if something is
> cached? Is there a way to see what's in cache?
No. The OS caches the data as read from the disk. If you need the data to be in memory
for performance then you need to make sure you have enough available RAM to hold
your typical result sets if possible.
> What about the postgresql.conf config settings. This is what I have and why.
>
> sort_mem = 32768
>
> This is default.
This is not the default. The default is 1000. You are telling Postgres to use 32Megs for
*each* sort that is taking place. If you have several queries each performing large sorts
you can quickly eat up available RAM this way. If you will only have a small number of
concurrrent queries performing sorts then this may be OK. Don't forget, a single query
can perform more than one sort operation. If you have 10 large sorts happening at the
same time, you can eat up to 320 megs this way!
You will need to tell us the number of updates/deletes you are having. This will
determine the vacuum needs. If the bulk of the data is inserted you may only need to
analyze frequently, not vacuum.
In order to get more help you will need to supply the update/delete frequency and the
explain analyze output from your queries.
Regards,
Gary.