On Thu, 29 Apr 2004 09:48:10 -0400 (EDT), Edmund Dengler
<edmundd@eSentire.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