From: Tom Lane <tgl@sss.pgh.pa.us>
To: Matt Smiley <mss@rentrak.com>
Cc: david.west@cusppoint.com
Cc: pgsql-performance@postgresql.org
Subject: Re: limit clause breaks query planner?
Date: Tue, 02 Sep 2008 17:00:48 -0400
Message-ID: <5925.1220389248@sss.pgh.pa.us> (raw)
In-Reply-To: <48BD33B2.D078.0028.0@rentrak.com>
References: <48BD33B2.D078.0028.0@rentrak.com>
"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
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, mss@rentrak.com, david.west@cusppoint.com
Subject: Re: limit clause breaks query planner?
In-Reply-To: <5925.1220389248@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