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 05:15:32
Message-ID: 15675.1230009332@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
"Shane Wright" <shane(dot)wright(at)edigitalresearch(dot)com> writes:
> It's not that it isn't using any index (so enable_seqscan=off doesn't
> help), it's that the index it picks is suboptimal.

> The query is based on 3 of the table columns - there is an index on all
> three, but it prefers to use an index on just two of them, then
> filtering by the 3rd (EXPLAIN output is below - showing the query as
> well).

Well, that isn't ipso facto a stupid choice to make.  The 3-column
index is larger and slower to search, so it should only use it if
there's a substantial reduction in the number of heap rows to be
visited.  You say that there is, but the estimated costs in the EXPLAINs
you provide sure don't indicate that the planner thinks so.  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).
Getting those estimates to match reality is the key to getting a sane
plan choice here.

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Shane WrightDate: 2008-12-23 09:56:03
Subject: Re: Using the wrong index (very suboptimal), why?
Previous:From: Craig RingerDate: 2008-12-23 02:27:57
Subject: Re: Backup Policy & Disk Space Issues

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