Re: Unable to use index? - Mailing list pgsql-general

From Manfred Koizar
Subject Re: Unable to use index?
Date
Msg-id qt9290h3lfvkiitt4muiblbg882ua7r4sj@email.aon.at
Whole thread Raw
In response to Unable to use index?  (Edmund Dengler <edmundd@eSentire.com>)
Responses Re: Unable to use index?
List pgsql-general
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

pgsql-general by date:

Previous
From: Oleg Bartunov
Date:
Subject: Re: Postgre and Web Request
Next
From: Ryan Booz
Date:
Subject: Syntax error at or near "$1"