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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux