Postgres Pro
Downloads
Home   >   mailing lists

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

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

From
Joel Jacobson
Date:
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

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

From
Robert Haas
Date:
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