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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-general by date

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