Re: indexes are fucked

On Tue, 2005-08-02 at 10:50 -0700, Dr NoName wrote:
> > What is the output of these:
> >
> > set enable_seqscan = off;
> > explain SELECT render.* FROM render WHERE person_id
> > = 432;
>
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using render_person_id_idx on render
> (cost=0.00..108735.88 rows=27833 width=1493) (actual
> time=0.11..77.62 rows=5261 loops=1)
> Index Cond: (person_id = 432)
> Total runtime: 80.99 msec
> (3 rows)
>
>
> so it will use the index if I disable seq scan? wtf?

Setting enable_seqscan to off artificially adds a
high fake cost factor to seqscans, so the planner will
not use them, unless there is no alternative.
This usually should not be done in production, but
can be useful for debugging.
Here we see that the planner estimated 27833 rows,
but actually only 5261 rows were retrieved.
Based on the high number of rows, a cost of 108735
was estimated.

>
>
> > set enable_seqscan = on;
> > explain SELECT render.* FROM render WHERE person_id
> > = 432;
>
> QUERY PLAN
> --------------------------------------------------------------------------------------------------------------
> Seq Scan on render (cost=0.00..39014.72 rows=27833
> width=1493) (actual time=7.11..743.55 rows=5261
> loops=1)
> Filter: (person_id = 432)
> Total runtime: 747.42 msec
> (3 rows)

the seqscan is cheaper when a large enough proportion
(maybe 5%) of rows are retrieved, and indeed the cost
is estimated at 39014

try to increase statistics for this column:

ALTER TABLE render ALTER COLUMN person_id
SET STATISTICS 1000;
ANALYZE render;

1000 is the maximum value, and probably overkill,
but you might start with that. If this helps, you can
try to lower values until you find the lowest one
that still suits your data. Usually, 200 is enough.

gnari