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: Thu, 04 Sep 2008 11:32:20 -0400
Message-ID: <23341.1220542340@sss.pgh.pa.us> (raw)
In-Reply-To: <48BF195E.D078.0028.0@rentrak.com>
References: <48BD33B2.D078.0028.0@rentrak.com>
<48BD33B2.D078.0028.0@rentrak.com>
<5925.1220389248@sss.pgh.pa.us>
<48BF195E.D078.0028.0@rentrak.com>
"Matt Smiley" <mss@rentrak.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us> writes:
>> 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 looks like the bitmap scan has a higher cost estimate because the
> entire bitmap index must be built before beginning the heap scan and
> returning rows up the pipeline.
Oh, of course. The LIMIT is small enough to make it look like we can
get the required rows after scanning only a small part of the table,
so the bitmap scan will lose out in the cost comparison because of its
high startup cost.
Ultimately the only way that we could get the right answer would be if
the planner realized that the required rows are concentrated at the end
of the table instead of being randomly scattered. This isn't something
that is considered at all right now in seqscan cost estimates. I'm not
sure offhand whether the existing correlation stats would be of use for
it, or whether we'd have to get ANALYZE to gather additional data.
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: <23341.1220542340@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