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

"William N. Zanatta" <william@veritel.com.br> writes:
> 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)

What do you get from

explain analyze select * from tbl_access where
((ip >= '12'::character varying) AND (ip < > '13'::character varying))

A deficiency in the EXPLAIN output format is that you can only see the
planner's estimate of the final number of output rows at a node --- in
this case, the "139" includes what it thinks the LIKE operator will do.
I suspect that its estimate of the number of rows that would be found
in the indexscan is far greater --- and, probably, far off.

Have you done an ANALYZE on this table lately?  How many rows in the
table altogether?

            regards, tom lane