From: David West <david.west@cusppoint.com>
To: pgsql-performance@postgresql.org
Subject: Re: limit clause breaks query planner?
Date: Tue, 2 Sep 2008 10:48:07 +0100
Message-ID: <006901c90ce1$00fa5330$02eef990$@west@cusppoint.com> (raw)
In-Reply-To: <87prnmor13.fsf@mnc.ch>
References: <1683189597512580954@unknownmsgid>
<162867790809010552o624a347aif0eb5fc71e7bbad@mail.gmail.com>
<2934211014530751484@unknownmsgid>
<162867790809011217n587e6090qdec2c4c6a1f580e6@mail.gmail.com>
<48BCB90D.8090606@pws.com.au>
<87prnmor13.fsf@mnc.ch>
Yes I inserted values in big batches according to a single value of 'B', so
indeed a sequence scan may have to scan forward many millions of rows before
finding the required value.
I have been doing regular analyse commands on my table. I don't think my
table is bloated, I haven't been performing updates. However I'm doing a
vacuum analyse now and I'll see if that makes any difference.
I am using postgres 8.3.1 with a default install on windows - no tweaks to
the configuration at all.
There are many other columns in my table, but none of them are used in this
query.
Guillaume in your example you didn't add the limit clause? Postgres chooses
the correct index in my case without the limit clause, the problem is with
the limit clause. One other difference with your example is both my columns
are varchar columns, not integer and text, I don't know if that would make a
difference.
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: david.west@cusppoint.com
Subject: Re: limit clause breaks query planner?
In-Reply-To: <006901c90ce1$00fa5330$02eef990$@west@cusppoint.com>
* 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