Re: Help on query plan.

"William N. Zanatta" <william(at)veritel(dot)com(dot)br> writes:
> And now, the same query with enable_seqscan set to OFF.

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

> ----------------------------------------------------------------------------------------------------------------------------------
> Index Scan using teste1 on tbl_access (cost=0.00..63182.79 rows=16968
> width=133) (actual time=0.91..1813.32 rows=20318 loops=1)
> Index Cond: ((ip >= '12'::character varying) AND (ip <
> '13'::character varying))
> Total runtime: 1863.33 msec

Okay, so the rows estimate isn't too far off (17k vs 20k) --- you could
doubtless make it more accurate by increasing the statistics target, but
that won't help here (the cost estimate would actually go up).

So the index is selecting about 1.7% of the table in this case. For
randomly-scattered rows, that's usually near the threshold of where an
indexscan stops making sense. Given the very low actual runtime of the
indexscan, I have to guess that the data is not randomly scattered but
is actually pretty well clustered --- and that the planner is failing
to account for that effect adequately.

What does pg_stats show as the correlation value for the ip column?
If you increase the statistics target and re-ANALYZE, does the
correlation value change?

regards, tom lane