Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 92D70633F9C for ; Fri, 2 Apr 2010 15:24:19 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 14023-03 for ; Fri, 2 Apr 2010 18:24:08 +0000 (UTC) X-Greylist: delayed 00:05:06.153397 by SQLgrey-1.7.6 Received: from mail-ww0-f46.google.com (mail-ww0-f46.google.com [74.125.82.46]) by mail.postgresql.org (Postfix) with ESMTP id B45F7633E32 for ; Fri, 2 Apr 2010 15:24:08 -0300 (ADT) Received: by wwb17 with SMTP id 17so1516364wwb.19 for ; Fri, 02 Apr 2010 11:24:07 -0700 (PDT) MIME-Version: 1.0 Received: by 10.216.50.131 with HTTP; Fri, 2 Apr 2010 11:19:00 -0700 (PDT) Date: Fri, 2 Apr 2010 20:19:00 +0200 Received: by 10.216.186.138 with SMTP id w10mr1367273wem.206.1270232340853; Fri, 02 Apr 2010 11:19:00 -0700 (PDT) Message-ID: Subject: LIMIT causes planner to do Index Scan using a less optimal index From: Joel Jacobson To: pgsql-performance@postgresql.org Content-Type: multipart/alternative; boundary=0016367fa6a27574df0483450372 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.114 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_FONT_FACE_BAD=0.884, HTML_MESSAGE=0.001, SARE_BAYES_5x7=0.6 X-Spam-Level: X-Archive-Number: 201004/52 X-Sequence-Number: 38302 --0016367fa6a27574df0483450372 Content-Type: text/plain; charset=ISO-8859-1 I came across a strange problem when writing a plpgsql function. Why won't the query planner realize it would be a lot faster to use the "index_transactions_accountid_currency" index instead of using the "transactions_pkey" index in the queries below? The LIMIT 1 part of the query slows it down from 0.07 ms to 1023 ms. Is this a bug? I'm using version 8.4.1. db=# SELECT TransactionID FROM Transactions WHERE AccountID = 108 AND Currency = 'SEK' ORDER BY TransactionID; transactionid --------------- 2870130 2870164 3371529 3371545 3371565 (5 rows) db=# EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE AccountID = 108 AND Currency = 'SEK' ORDER BY TransactionID; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------- Sort (cost=27106.33..27134.69 rows=11345 width=4) (actual time=0.048..0.049 rows=5 loops=1) Sort Key: transactionid Sort Method: quicksort Memory: 25kB -> Bitmap Heap Scan on transactions (cost=213.39..26342.26 rows=11345 width=4) (actual time=0.033..0.039 rows=5 loops=1) Recheck Cond: ((accountid = 108) AND (currency = 'SEK'::bpchar)) -> Bitmap Index Scan on index_transactions_accountid_currency (cost=0.00..210.56 rows=11345 width=0) (actual time=0.027..0.027 rows=5 loops=1) Index Cond: ((accountid = 108) AND (currency = 'SEK'::bpchar)) Total runtime: 0.070 ms (8 rows) db=# SELECT TransactionID FROM Transactions WHERE AccountID = 108 AND Currency = 'SEK' ORDER BY TransactionID LIMIT 1; transactionid --------------- 2870130 (1 row) db=# EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE AccountID = 108 AND Currency = 'SEK' ORDER BY TransactionID LIMIT 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..43.46 rows=1 width=4) (actual time=1023.213..1023.214 rows=1 loops=1) -> Index Scan using transactions_pkey on transactions (cost=0.00..493029.74 rows=11345 width=4) (actual time=1023.212..1023.212 rows=1 loops=1) Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar)) Total runtime: 1023.244 ms (4 rows) db=# \d transactions Table "public.transactions" Column | Type | Modifiers -------------------------------+--------------------------+------------------------------------------------------- transactionid | integer | not null default nextval('seqtransactions'::regclass) eventid | integer | not null ruleid | integer | not null accountid | integer | not null amount | numeric | not null balance | numeric | not null currency | character(3) | not null recorddate | timestamp with time zone | not null default now() Indexes: "transactions_pkey" PRIMARY KEY, btree (transactionid) "index_transactions_accountid_currency" btree (accountid, currency) "index_transactions_eventid" btree (eventid) Foreign-key constraints: "transactions_accountid_fkey" FOREIGN KEY (accountid) REFERENCES accounts(accountid) DEFERRABLE "transactions_eventid_fkey" FOREIGN KEY (eventid) REFERENCES events(eventid) DEFERRABLE "transactions_ruleid_fkey" FOREIGN KEY (ruleid) REFERENCES rules(ruleid) DEFERRABLE -- Best regards, Joel Jacobson --0016367fa6a27574df0483450372 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable I came across a strange problem when writing a plpgsql function.

Why won't the query planner realize it would be a lot faster t= o use the "index_transactions_accountid_currency" index instead o= f using the "transactions_pkey" index in the queries below?
The LIMIT 1 part of the query slows it down from 0.07 ms to 1023 ms.

Is this a bug?=A0I'm using version 8.4.1.
=

db=3D# SELECT TransactionID FROM Transa= ctions WHERE AccountID =3D 108 AND Currency =3D 'SEK' ORDER BY Tran= sactionID;
=A0transactionid=A0
---------------
=
=A0=A0 =A0 =A0 2870130
=A0=A0 =A0 =A0 2870164
=A0=A0 =A0 =A0 3371529<= /div>
=A0=A0 =A0 =A0 3371545
=A0=A0 =A0 =A0 3371565
(5 rows)

db=3D# EXPLAIN ANALYZE SELECT TransactionID FROM Transact= ions WHERE AccountID =3D 108 AND Currency =3D 'SEK' ORDER BY Transa= ctionID;
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0QUERY PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0
----------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------
=A0Sort =A0(cost=3D27106.33..27134.69 rows=3D11345 width=3D4) (actual = time=3D0.048..0.049 rows=3D5 loops=3D1)
=A0=A0 Sort Key: t= ransactionid
=A0=A0 Sort Method: =A0quicksort =A0Memory: 25kB
=A0= =A0 -> =A0Bitmap Heap Scan on transactions =A0(cost=3D213.39..26342.26 r= ows=3D11345 width=3D4) (actual time=3D0.033..0.039 rows=3D5 loops=3D1)
=A0=A0 =A0 =A0 =A0 Recheck Cond: ((accountid =3D 108) AND (currency = =3D 'SEK'::bpchar))
=A0=A0 =A0 =A0 =A0 -> =A0Bi= tmap Index Scan on index_transactions_accountid_currency =A0(cost=3D0.00..2= 10.56 rows=3D11345 width=3D0) (actual time=3D0.027..0.027 rows=3D5 loops=3D= 1)
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 Index Cond: ((accountid =3D 108) AND (c= urrency =3D 'SEK'::bpchar))
=A0Total runtime: 0.07= 0 ms
(8 rows)

db=3D# SELECT Transact= ionID FROM Transactions WHERE AccountID =3D 108 AND Currency =3D 'SEK&#= 39; ORDER BY TransactionID LIMIT 1;
=A0transactionid=A0
---------------
= = =A0=A0 =A0 =A0 2870130
(1 row)

db=3D# EXPLAIN ANALYZE = SELECT TransactionID FROM Transactions WHERE AccountID =3D 108 AND Currency= =3D 'SEK' ORDER BY TransactionID LIMIT 1;
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 QUERY = PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
----------------------------------------------------------------------= ---------------------------------------------------------------------------= ----
=A0Limit =A0(cost=3D0.00..43.46 rows=3D1 width=3D4) (actual time=3D102= 3.213..1023.214 rows=3D1 loops=3D1)
=A0=A0 -> =A0Index = Scan using transactions_pkey on transactions =A0(cost=3D0.00..493029.74 row= s=3D11345 width=3D4) (actual time=3D1023.212..1023.212 rows=3D1 loops=3D1)<= /font>
=A0=A0 =A0 =A0 =A0 Filter: ((accountid =3D 108) AND (currency =3D '= ;SEK'::bpchar))
=A0Total runtime: 1023.244 ms<= /div>
(4 rows)

db=3D# \d transactions=
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0 =A0 =A0 Table "public.transactions"
=A0= =A0 =A0 =A0 =A0 =A0 =A0Column =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0= Type =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 Mod= ifiers =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=A0
-------------------------------+--------------------------+-----------= --------------------------------------------
=A0transact= ionid =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | integer =A0 =A0 =A0 =A0 =A0 =A0 =A0= =A0 =A0| not null default nextval('seqtransactions'::regclass)
=A0eventid =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | integer =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| not null
=A0ruleid =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| integer =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0 =A0| not null
=A0accountid =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | integer =A0 =A0= =A0 =A0 =A0 =A0 =A0 =A0 =A0| not null
=A0amount =A0 =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| numeric =A0 =A0 =A0 =A0 =A0 =A0 = =A0 =A0 =A0| not null
=A0balance =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | numeric =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| not null
=A0currency =A0 = =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| character(3) =A0 =A0 =A0 =A0 =A0 = =A0 | not null
=A0recorddate =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| timestamp with = time zone | not null default now()
Indexes= :
=A0=A0 =A0"transactions_pkey" PRIMARY KEY, btree (transactio= nid)
=A0=A0 =A0"index_transactions_accountid_currency= " btree (accountid, currency)
=A0=A0 =A0"index_transactions_eventid" btree= (eventid)
Foreign-key constraints:
=A0=A0 =A0"transactions_accountid_fkey" FOREIGN KEY (account= id) REFERENCES accounts(accountid) DEFERRABLE
=A0=A0 =A0"transactions_eventid_fkey" FOREIGN KEY (eventid) = REFERENCES events(eventid) DEFERRABLE
=A0=A0 =A0"transactions_ruleid_fkey" FOREIGN KEY (ruleid) RE= FERENCES rules(ruleid) DEFERRABLE


--
Best regards,

Joel Jacobson

--0016367fa6a27574df0483450372--