From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dave Cramer <pg@fastcrypt.com>
Cc: postgresql performance list <pgsql-performance@postgresql.org>
Subject: Re: Planner won't use composite index if there is an order by ????
Date: Thu, 24 Apr 2008 20:23:27 -0400
Message-ID: <11004.1209083007@sss.pgh.pa.us> (raw)
In-Reply-To: <DECED72A-5784-4D6C-9E27-6316FDE32E64@fastcrypt.com>
References: <4810E914.9030109@ourstage.com>
<DECED72A-5784-4D6C-9E27-6316FDE32E64@fastcrypt.com>
Dave Cramer <pg@fastcrypt.com> writes:
> Any idea why it wouldn't choose the right index ?
It thinks that way is cheaper. It's estimating its cost at 8381,
whereas selecting all the rows then sorting must take 10131 plus
some time to sort.
The reason why those estimates are so far off from reality is directly
tied to the 400-estimated-vs-2-actual rowcount estimation error.
I'm not sure how much of that could be fixed by raising the stats target
for the table, but that's certainly something to try.
Another thing you should look at is eliminating dependences between
columns. I'll bet that the "scored_at is not null" condition is either
redundant with the "score in ..." condition, or could be made so (ie
force score to null when scored_at is null). If you could get rid of
the separate test on scored_at, it'd help to avoid the estimation weak
spot of correlated restrictions.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, pg@fastcrypt.com
Subject: Re: Planner won't use composite index if there is an order by ????
In-Reply-To: <11004.1209083007@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox