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
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 |