Re: Help on query plan. (was: select like and indexes)

On Mon, 20 Jan 2003, William N. Zanatta wrote:

>
> access=# explain analyze select * from tbl_access where ip like '12%157';
> QUERY PLAN
> -------------------------------------------------------------------
> Seq Scan on tbl_access (cost=0.00..42519.84 rows=139 width=134)
> (actual time=698.03..20504.07 rows=1391 loops=1)
> Filter: (ip ~~ '12%157'::text)
> Total runtime: 20507.44 msec
> (3 rows)
>
> -------------------------------------------------------------------
>
> access=# SET enable_seqscan = off;
> SET
> access=# explain analyze select * from tbl_access where ip like '12%157';
> QUERY PLAN
>
> -------------------------------------------------------------------
> Index Scan using teste1 on tbl_access (cost=0.00..63593.03 rows=139
> width=134) (actual time=160.69..1177.26 rows=1391 loops=1)
> Index Cond: ((ip >= '12'::character varying) AND (ip <
> '13'::character varying))
> Filter: (ip ~~ '12%157'::text)
> Total runtime: 1181.18 msec
> (4 rows)
>
>

Okay, this shows that you are already in "C" locale, since otherwise it
wouldn't try it at all. Have you done a vacuum analyze recently?
What does vacuum analyze verbose tbl_access; give you?

The real rows and estimated rows seem far enough off that it might be
getting confused as to which plan is best.