Обсуждение: LIMIT causes planner to do Index Scan using a less optimal index
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
On Fri, Apr 2, 2010 at 2:19 PM, Joel Jacobson <joel@gluefinance.com> wrote: > Is this a bug? I'm using version 8.4.1. It's not really a bug, but it's definitely not a feature either. > 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) The planner's idea here is that rows matching the filter criteria will be common enough that an index scan over transactions_pkey will find one fairly quickly, at which point the executor can return that row and stop. But it turns out that those rows aren't as common as the planner thinks, so the search takes a long time. ...Robert