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

"William N. Zanatta" <william(at)veritel(dot)com(dot)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