Re: Using the wrong index (very suboptimal), why?

"Shane Wright" <shane.wright@edigitalresearch.com> writes:
> Tom,
>> You need to
>> look into what the estimated vs actual rowcounts are for just the
>> two-column condition (ie, where nid = something AND iid1 = something).

> Improving the accuracy of the cost estimates is exactly what I'm trying
> to achieve, so far I've tried....

> - increasing the statistics target to the maximum setting with SET
> STATISTICS 1000 on columns rid, nid and iid1 of answers, then
> re-vacuuming

I hope you meant re-analyzing.

> emystery=# explain analyse select * FROM ecos_answers WHERE
> nid=253869913 AND iid1=535292129;

> QUERY PLAN
> ------------------------------------------------------------------------
> ------------------------------------------------------------------------
> ---
>  Index Scan using index_ecos_answers_nidiid1 on ecos_answers
> (cost=0.00..114.14 rows=1 width=60) (actual time=0.206..2398.645
> rows=21891 loops=1)
>    Index Cond: ((nid = 253869913) AND (iid1 = 535292129))
>  Total runtime: 2424.769 ms
> (3 rows)

Well, here's the problem all right: 1 row estimated vs 21891 actual.
Is there something odd about the joint distribution of these two
columns?

            regards, tom lane