Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 6F8A164FCDF for ; Mon, 1 Sep 2008 09:31:30 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 80823-10 for ; Mon, 1 Sep 2008 09:31:12 -0300 (ADT) X-Greylist: delayed 00:12:28.574257 by SQLgrey-1.7.6 Received: from mail.cusppoint.com (mail.cusppoint.com [193.120.252.200]) by postgresql.org (Postfix) with ESMTP id 2BBB264FCD2 for ; Mon, 1 Sep 2008 09:31:16 -0300 (ADT) Received: from BREMEN ([194.125.14.201]) by mail.cusppoint.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 1 Sep 2008 13:16:51 +0100 From: "David West" To: Subject: limit clause breaks query planner? Date: Mon, 1 Sep 2008 13:18:33 +0100 Message-ID: <002d01c90c2c$dc9f94f0$95debed0$@west@cusppoint.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_002E_01C90C35.3E63FCF0" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AckMLNlOSHdscX5FTJCrxa854d7HJQ== Content-Language: en-ie X-OriginalArrivalTime: 01 Sep 2008 12:16:51.0593 (UTC) FILETIME=[9D6B7790:01C90C2C] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.212 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, MSGID_MULTIPLE_AT=1.211 X-Spam-Level: * X-Archive-Number: 200809/5 X-Sequence-Number: 31318 This is a multipart message in MIME format. ------=_NextPart_000_002E_01C90C35.3E63FCF0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_002E_01C90C35.3E63FCF0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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 =3D ‘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=3D0.00..3.69 rows=3D15 = width=3D128)"

" -> Seq Scan on my_table = this_ (cost=3D0.00..252424.24 rows=3D1025157 width=3D128)"

" = Filter: ((A IS NULL) AND ((B)::text =3D '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=3Dfalse) 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

------=_NextPart_000_002E_01C90C35.3E63FCF0--