LIMIT causes planner to do Index Scan using a less optimal index

From: Joel Jacobson <joel(at)gluefinance(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: LIMIT causes planner to do Index Scan using a less optimal index
Date: 2010-04-02 18:19:00
Message-ID: o2n8bdec0841004021119u69517c9fm9d239dc36b6af397@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Christiaan Willemsen 2010-04-02 19:15:00 Using high speed swap to improve performance?
Previous Message Scott Carey 2010-04-01 21:00:24 Re: Database size growing over time and leads to performance impact