Re: Slow SELECT

Mat <psql-mail(at)freeuk(dot)com> writes:
> On Fri, 2003-10-03 at 17:50, Tom Lane wrote:
>> Well, it seems to be running at about 5 msec/row, which would be quite
>> respectable if each fetch required another disk seek. I'm wondering why
>> you are (apparently) not managing to get more than one row per page
>> fetched. What are your configuration settings --- particularly
>> shared_buffers? Could we see the output of VACUUM VERBOSE for this
>> table?

> Lines from postgresql.conf that don't start with a '#':
> shared_buffers = 126976 #992 MB

As someone else pointed out, that is way too large (unless maybe you
have 4Gb of RAM, and even then I'd not counsel making shared_buffers
that large).

> INFO: --Relation public.meta--
> INFO: Pages 685043: Changed 0, Empty 8; Tup 5999170: Vac 0, Keep 0,
> UnUsed 5999170.
> Total CPU 18.06s/3.61u sec elapsed 612.91 sec.

This shows you've got less than 9 tuples per 8k disk page on average.
Is the table very wide? If it doesn't seem to you that the tuples
should occupy 1K apiece, it might be that you need to run a VACUUM FULL
to get rid of some excess free space. (If so, it's a sign that you need
to increase the FSM settings in postgresql.conf and/or run plain VACUUM
more frequently, so that the free space doesn't get away from you
again.)

regards, tom lane