Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group