Re: postgres 7.4 at 100%

Chris Cheston <ccheston(at)gmail(dot)com> writes:
> Wow, this simple query is taking 676.24 ms to execute! it only takes
> 18 ms on our other machine.

> This table has 150,000 rows. Is this normal?

> live=# explain analyze SELECT id FROM calllogs WHERE from = 'you';
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------
> Seq Scan on calllogs (cost=0.00..136.11 rows=24 width=4) (actual
> time=0.30..574.72 rows=143485 loops=1)
> Filter: (from = 'you'::character varying)
> Total runtime: 676.24 msec
> (3 rows)

So the query is pulling 140K+ rows out of a table with 150K entries?
No chance that an index will help for that. You're fortunate that the
thing did not try to use an index though, because it thinks there are
only 24 rows matching 'you', which is one of the more spectacular
statistical failures I've seen lately. I take it you haven't ANALYZEd
this table in a long time?

It is hard to believe that your other machine can pull 140K+ rows in
18 msec, though. Are you sure the table contents are the same in both
cases?

If they are, the only reason I can think of for the discrepancy is a
large amount of dead space in this copy of the table. What does VACUUM
VERBOSE show for it, and how does that compare to what you see on the
other machine? Try a CLUSTER or VACUUM FULL to see if you can shrink
the table's physical size (number of pages).

regards, tom lane