Re: slow query performance

"Dave Weaver" <davew@wsieurope.com> writes:
>> The output of VACUUM VERBOSE for this table would be useful to show.

> NOTICE:  Index obs_pkey: Pages 114962; Tuples 13739326: Deleted 30881. CPU
> 13.24s/19.80u sec.

Lets see, 114962 pages at 8K apiece, divided by 13739326 entries, gives
about 68 bytes per index entry. The entries themselves (contents a
timestamp and a char(10)) take 8 + 4 + 10 bytes for data, plus 8 bytes
for the index tuple header, plus 2 bytes wasted for alignment (assuming
this is Intel hardware), plus a 4-byte line pointer; 36 bytes total.
So you have an index loading factor of about 52%, which is noticeably
less than the theoretical optimum of 70%, though not really bad yet.
I think you are seeing some index bloat --- especially if you
recently reindexed, meaning that the index hasn't had very long to
grow.  You could try keeping an eye on the size of obs_pkey over time
and see if it grows faster than the table itself.

If you are going to upgrade I'd counsel going to 7.4, which should solve
or at least greatly reduce the problem of index bloat.  See the
pgsql-performance archives for more discussion.

            regards, tom lane