Postgres Pro
Downloads
Home   >   mailing lists

Re: Re: Index scan and bitmap index scan - hard to understand how planner chooses - Mailing list pgsql-general

From Jeff Janes
Subject Re: Re: Index scan and bitmap index scan - hard to understand how planner chooses
Date
Msg-id CAMkU=1y3STe7eJ19GPVQvytKJvmRGy-dm9oCkfGUbuqwbsHNzA@mail.gmail.com
Whole thread Raw
In response to Re: Index scan and bitmap index scan - hard to understand how planner chooses  (boraldomaster <boraldomaster@gmail.com>)
Responses Re: Index scan and bitmap index scan - hard to understand how planner chooses  (boraldomaster <boraldomaster@gmail.com>)
List pgsql-general
Tree view


On Mon, Jun 24, 2013 at 5:42 AM, boraldomaster <boraldomaster@gmail.com> wrote:
This seems to be fixed in 9.2.


Which version were you originally seeing it in?  I still see that behavior (or something close to it) in 9.4dev.

It stabilizes after doing a "vacuum analyze" (not just "analyze"), but that is because it then switches to Index Only Scan.

I haven't pinned it down exactly, but it seems that there are two issues.

One is that there are two estimates of the row returned, one for the whole query (which is visible in EXPLAIN, and is spot on) and one for the index patch, which is very wrong for 0000% case (the estimate was 1 row rather than 100, visible only in the debugger or with special logging code added).  The other is that the correlation between index/column order and table order is thought to be 0 when it is really 1.

Cheers,

Jeff

pgsql-general by date:

Previous
From: Jan Wieck
Date:
Subject: Re: .pgpass being ignored
Next
From: Stuart Ford
Date:
Subject: Re: pg_largeobject.sql script not run after upgrade