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