LIMIT causes planner to do Index Scan using a less optimal index - Mailing list pgsql-performance
| From | Joel Jacobson |
|---|---|
| Subject | LIMIT causes planner to do Index Scan using a less optimal index |
| Date | |
| Msg-id | o2n8bdec0841004021119u69517c9fm9d239dc36b6af397@mail.gmail.com Whole thread Raw |
| Responses |
Re: LIMIT causes planner to do Index Scan using a less
optimal index
(Robert Haas <robertmhaas@gmail.com>)
|
| List | pgsql-performance |
I came across a strange problem when writing a plpgsql function.
--
Best regards,
Joel Jacobson
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
pgsql-performance by date: