DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=KatkGhxfk/fGecsjRFJKmHsueS4D/afM8ASgouZpbnS8xhaT4DI8FMkDfaFVF/j026
	TihRTR9Nfp9dtxE8ZGztO3q5hyQ37x0fnDtWo82QD0oreTNqkgUR4vZz/WZXyqDXruTZ
	vhnB/OyT2hSMO5IhAoGt3n6L6UdQ20+mQDTAM=
Message-ID: <162867790809010552o624a347aif0eb5fc71e7bbad@mail.gmail.com>
Date: Mon, 1 Sep 2008 14:52:53 +0200
From: "Pavel Stehule" <pavel.stehule@gmail.com>
To: "David West" <david.west@cusppoint.com>
Subject: Re: limit clause breaks query planner?
Cc: pgsql-performance@postgresql.org
In-Reply-To: <1683189597512580954@unknownmsgid>
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <1683189597512580954@unknownmsgid>

Hello

you should partial index

create index foo(b) on mytable where a is null;

regards
Pavel Stehule

2008/9/1 David West <david.west@cusppoint.com>:
> Hi,
>
>
>
> I have a single table with about 10 million rows, and two indexes.  Index A
> is on a column A with 95% null values.  Index B is on a column B with about
> 10 values, ie. About a million rows of each value.
>
>
>
> When I do a simple query on the table (no joins) with the following
> condition:
>
> A is null AND
>
> B = '21'
>
>
>
> it uses the correct index, index B.  However, when I add a limit clause of
> 15, postgres decides to do a sequential scan :s.  Looking at the results
> from explain:
>
>
>
> "Limit  (cost=0.00..3.69 rows=15 width=128)"
>
> "  ->  Seq Scan on my_table this_  (cost=0.00..252424.24 rows=1025157
> width=128)"
>
> "        Filter: ((A IS NULL) AND ((B)::text = '21'::text))"
>
>
>
> It appears that postgres is (very incorrectly) assuming that it will only
> have to retrieve 15 rows on a sequential scan, and gives a total cost of
> 3.69.  In reality, it has to scan many rows forward until it finds the
> correct value, yielding very poor performance for my table.
>
>
>
> If I disable sequential scan (set enable_seqscan=false) it then incorrectly
> uses the index A that has 95% null values: it seems to incorrectly apply the
> same logic again that it will only have to retrieve 15 rows with the limit
> clause, and thinks that the index scan using A is faster than index scan B.
>
>
>
> Only by deleting the index on A and disabling sequential scan will it use
> the correct index, which is of course by far the fastest.
>
>
>
> Is there an assumption in the planner that a limit of 15 will mean that
> postgres will only have to read 15 rows?  If so is this a bad assumption?
> If a particular query is faster without a limit, then surely it will also be
> faster with the limit.
>
>
>
> Any workarounds for this?
>
>
>
> Thanks
>
> David