Re: Unable to use index?

On Thu, 29 Apr 2004 09:48:10 -0400 (EDT), Edmund Dengler
<edmundd(at)eSentire(dot)com> wrote:
>=> explain analyze select * from replicated where rep_component = 'ps_probe' limit 1;
>-------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..0.23 rows=1 width=101) (actual time=34401.857..34401.859 rows=1 loops=1)
> -> Seq Scan on replicated (cost=0.00..936557.70 rows=4114363 width=101) (actual time=34401.849..34401.849 rows=1 loops=1)
^^^^
> Filter: ((rep_component)::text = 'ps_probe'::text)

The planner thinks that the seq scan has a startup cost of 0.00, i.e.
that it can return the first tuple immediately, which is obviously not
true in the presence of a filter condition. Unfortunately there's no
easy way to fix this, because the statistics information does not have
information about the physical position of tuples with certain vaules.

>=> explain analyze select * from replicated where rep_component = 'ps_probe' order by rep_component limit 1;

This is a good workaround. It makes the plan for a seq scan look like

| Limit (cost=2345679.00..2345679.20 rows=1 width=101)
| -> Sort (2345678.90..2500000.00 rows=4114363 width=101)
| -> Seq Scan on replicated (cost=0.00..936557.70 rows=4114363 width=101)
| Filter: ((rep_component)::text = 'ps_probe'::text)

which is a loser against the index scan:

> Limit (cost=0.00..1.66 rows=1 width=101) (actual time=51.163..51.165 rows=1 loops=1)

>Maybe I need to up the number of rows sampled for statistics?

Won't help, IMHO.

Servus
Manfred