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 ???? 
In-reply-to: <DECED72A-5784-4D6C-9E27-6316FDE32E64@fastcrypt.com> 
References: <4810E914.9030109@ourstage.com>
	<DECED72A-5784-4D6C-9E27-6316FDE32E64@fastcrypt.com>
Comments: In-reply-to Dave Cramer <pg@fastcrypt.com>
	message dated "Thu, 24 Apr 2008 20:06:01 -0400"
Date: Thu, 24 Apr 2008 20:23:27 -0400
Message-ID: <11004.1209083007@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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