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.ctr....@faa.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 <robertmh...@gmail.com> > cc > Joel Jacobson <j...@gluefinance.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 <robertmh...@gmail.com>* > > 04/06/2010 03:07 PM > To > Sherry CTR Zhu/AWA/CNTR/f...@faa > cc > Joel Jacobson <j...@gluefinance.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.ctr....@faa.gov*<sherry.ctr....@faa.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: j...@gluefinance.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