On Thu, 29 Apr 2004 09:48:10 -0400 (EDT), Edmund Dengler
<[EMAIL PROTECTED]> 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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to