On Fri, 19 Nov 2004, Marc Boucher wrote:
> At 16:23 18/11/2004 +0100, Martijn van Oosterhout wrote:
> > The system seems to think that a scan is cheap because the table is so
> > small.
> The table currently contains just over 10000 elements. So 238 rows is a
> small part of it.
>
> > Have you ever ANALYZEd that table?
> Yes. That and reindex (in case of a problem).
> I have also tried ALTER TABLE ALTER COLUMN SET STATISTICS, but without
> improvement.
>
> > Also, EXPLAIN ANALYZE gives a much better idea of what is going on...
> You mean ANALYZE VERBOSE ?
EXPLAIN ANALYZE is similar to EXPLAIN except that it runs the query
and gives real time per step and real row counts rather than the
estimates. It's very handy.
If random page reads are consistently less expensive than postgres
currently thinks, you may want to lower random_page_cost. EXPLAIN ANALYZE
is useful here because it lets you compare the real times versus the
estimated costs of both scan types when used with enable_seqscan=off.