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

Actually, swapping the order of the conditions did in fact make some
difference, strange.

I ran the query a couple of times for each variation to see if the
difference in speed was just a coincidence or a pattern. Looks like the
speed really is different.

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID =
108) AND (Currency = 'SEK')) ORDER BY TransactionID LIMIT 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1384.401..1384.402
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual time=1384.399..1384.399
rows=1 loops=1)
Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar))
Total runtime: 1384.431 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =
'SEK') AND (AccountID = 108)) ORDER BY TransactionID LIMIT 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1710.166..1710.167
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual time=1710.164..1710.164
rows=1 loops=1)
Filter: ((currency = 'SEK'::bpchar) AND (accountid = 108))
Total runtime: 1710.200 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID =
108) AND (Currency = 'SEK')) ORDER BY TransactionID LIMIT 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1366.526..1366.527
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual time=1366.525..1366.525
rows=1 loops=1)
Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar))
Total runtime: 1366.552 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =
'SEK') AND (AccountID = 108)) ORDER BY TransactionID LIMIT 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1685.395..1685.396
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual time=1685.394..1685.394
rows=1 loops=1)
Filter: ((currency = 'SEK'::bpchar) AND (accountid = 108))
Total runtime: 1685.423 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID =
108) AND (Currency = 'SEK')) ORDER BY TransactionID LIMIT 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1403.904..1403.905
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual time=1403.903..1403.903
rows=1 loops=1)
Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar))
Total runtime: 1403.931 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =
'SEK') AND (AccountID = 108)) ORDER BY TransactionID LIMIT 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1689.014..1689.014
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual time=1689.012..1689.012
rows=1 loops=1)
Filter: ((currency = 'SEK'::bpchar) AND (accountid = 108))
Total runtime: 1689.041 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID =
108) AND (Currency = 'SEK')) ORDER BY TransactionID LIMIT 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1378.322..1378.323
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual time=1378.320..1378.320
rows=1 loops=1)
Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar))
Total runtime: 1378.349 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =
'SEK') AND (AccountID = 108)) ORDER BY TransactionID LIMIT 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1696.830..1696.831
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual time=1696.828..1696.828
rows=1 loops=1)
Filter: ((currency = 'SEK'::bpchar) AND (accountid = 108))
Total runtime: 1696.858 ms
(4 rows)

2010/4/6 <Sherry(dot)CTR(dot)Zhu(at)faa(dot)gov>

>
> I mean the time you spent on prune which one is cheaper might be another
> cost.
> Thanks much!
>
> Xuefeng Zhu (Sherry)
> Crown Consulting Inc. -- Oracle DBA
> AIM Lab Data Team
> (703) 925-3192
>
>
>
> *Sherry CTR Zhu/AWA/CNTR/FAA*
> AJR-32, Aeronautical Information Mgmt Group
>
> 04/06/2010 03:13 PM
> To
> Robert Haas <robertmhaas(at)gmail(dot)com>
> cc
> Joel Jacobson <joel(at)gluefinance(dot)com>
> Subject
> Re: [PERFORM] LIMIT causes planner to do Index Scan using a less
> optimal indexLink<Notes:///852576860052CAFA/DABA975B9FB113EB852564B5001283EA/15C2483F84B5A6D0852576FD006911AC>
>
>
>
> Have you tried before?
>
> Thanks much!
>
> Xuefeng Zhu (Sherry)
> Crown Consulting Inc. -- Oracle DBA
> AIM Lab Data Team
> (703) 925-3192
>
>
>
> *Robert Haas <robertmhaas(at)gmail(dot)com>*
>
> 04/06/2010 03:07 PM
> To
> Sherry CTR Zhu/AWA/CNTR/FAA(at)FAA
> cc
> Joel Jacobson <joel(at)gluefinance(dot)com>
> Subject
> Re: [PERFORM] LIMIT causes planner to do Index Scan using a less
> optimal index
>
>
>
>
> On Tue, Apr 6, 2010 at 3:05 PM, <*Sherry(dot)CTR(dot)Zhu(at)faa(dot)gov*<Sherry(dot)CTR(dot)Zhu(at)faa(dot)gov>>
> wrote:
>
> Just curious,
>
> Switch the where condition to try to make difference.
>
> how about change
> ((accountid = 108) AND (currency = 'SEK'::bpchar))
> to
> ( (currency = 'SEK'::bpchar) AND (accountid = 108) ).
>
>
> In earlier version of Oracle, this was common knowledge that optimizer took
> the last condition index to use.
>
> Ignore me if you think this is no sence. I didn't have a time to read your
> guys' all emails.
>
> PostgreSQL doesn't behave that way - it guesses which order will be
> cheaper.
>
> ...Robert
>
>

--
Best regards,

Joel Jacobson
Glue Finance

E: jj(at)gluefinance(dot)com
T: +46 70 360 38 01

Postal address:
Glue Finance AB
Box 549
114 11 Stockholm
Sweden

Visiting address:
Glue Finance AB
Birger Jarlsgatan 14
114 34 Stockholm
Sweden