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

Hi Xuefeng,

You have misunderstood the problem.

The index used in the query not containing the "LIMIT 1" part, is "
index_transactions_accountid_currency", which is indeed a two column index.

The problem is this index is not used when using "LIMIT 1".

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

>
> Guys,
>
> Thanks for trying and opening your mind.
> If you want to know how Oracle addressed this issue, here it is: index
> on two columns. I remember that they told me in the training postgres has
> no this kind of index, can someone clarify?
>
> Thanks much!
>
> Xuefeng Zhu (Sherry)
> Crown Consulting Inc. -- Oracle DBA
> AIM Lab Data Team
> (703) 925-3192
>
>
>
> *Joel Jacobson <joel(at)gluefinance(dot)com>*
>
> 04/06/2010 06:30 PM
> To
> Sherry CTR Zhu/AWA/CNTR/FAA(at)FAA, pgsql-performance(at)postgresql(dot)org
> cc
> Robert Haas <robertmhaas(at)gmail(dot)com>
> Subject
> Re: [PERFORM] 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* <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* <robertmhaas(at)gmail(dot)com>>
> cc
> Joel Jacobson <*joel(at)gluefinance(dot)com* <joel(at)gluefinance(dot)com>>
> Subject
> Re: [PERFORM] LIMIT causes planner to do Index Scan using a less
> optimal index*Link*<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* <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* <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* <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
>
>

--
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