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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Shane Wright" <shane(dot)wright(at)edigitalresearch(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Using the wrong index (very suboptimal), why?
Date: 2008-12-23 13:59:03
Message-ID: 25082.1230040743@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Shane Wright" <shane(dot)wright(at)edigitalresearch(dot)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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jais Mathew 2008-12-23 14:03:20 having two database clusters?
Previous Message Shane Wright 2008-12-23 09:56:03 Re: Using the wrong index (very suboptimal), why?