To: "Matt Smiley" <mss@rentrak.com>
cc: david.west@cusppoint.com, pgsql-performance@postgresql.org
Subject: Re: limit clause breaks query planner? 
In-reply-to: <48BD33B2.D078.0028.0@rentrak.com> 
References: <48BD33B2.D078.0028.0@rentrak.com>
Comments: In-reply-to "Matt Smiley" <mss@rentrak.com>
	message dated "Tue, 02 Sep 2008 12:38:19 -0700"
Date: Tue, 02 Sep 2008 17:00:48 -0400
Message-ID: <5925.1220389248@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Matt Smiley" <mss@rentrak.com> writes:
>  So an Index Scan is always going to have a higher cost estimate than
>  an equivalent Seq Scan returning the same result rows (unless
>  random_page_cost is < 1).  That's why I think the planner is always
>  preferring the plan that uses a Seq Scan.

If that were the case, we'd never choose an indexscan at all...

It's true that a plain indexscan is not preferred for queries that will
return a large fraction of the table.  However, it should be willing to
use a bitmap scan for this query, given default cost settings (the
default cost settings will cause it to prefer bitmap scan for retrieving
up to about a third of the table, in my experience).  I too am confused
about why it doesn't prefer that choice in the OP's example.  It would
be interesting to alter the random_page_cost setting and see if he gets
different results.

			regards, tom lane