From: David West <david.west@cusppoint.com>
To: pgsql-performance@postgresql.org
Subject: limit clause breaks query planner?
Date: Mon, 1 Sep 2008 13:18:33 +0100
Message-ID: <002d01c90c2c$dc9f94f0$95debed0$@west@cusppoint.com> (raw)
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
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: <002d01c90c2c$dc9f94f0$95debed0$@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