From: "David West" <david.west@cusppoint.com>
To: <pgsql-performance@postgresql.org>
References: <1683189597512580954@unknownmsgid>
	<162867790809010552o624a347aif0eb5fc71e7bbad@mail.gmail.com>
	<2934211014530751484@unknownmsgid>
	<162867790809011217n587e6090qdec2c4c6a1f580e6@mail.gmail.com>
	<48BCB90D.8090606@pws.com.au>
	<87prnmor13.fsf@mnc.ch>
In-Reply-To: <87prnmor13.fsf@mnc.ch>
Subject: Re: limit clause breaks query planner?
Date: Tue, 2 Sep 2008 10:48:07 +0100
Message-ID: <006901c90ce1$00fa5330$02eef990$@west@cusppoint.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AckM2vzFXwL2y8atR9C/1I2G7iv5qQABD0rA
Content-Language: en-ie

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.