Skip site navigation (1) Skip section navigation (2)

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

From: Sherry(dot)CTR(dot)Zhu(at)faa(dot)gov
To: Joel Jacobson <joel(at)gluefinance(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org,Robert Haas <robertmhaas(at)gmail(dot)com>
Subject: Re: LIMIT causes planner to do Index Scan using a less optimal index
Date: 2010-04-07 12:20:54
Message-ID: OF1E8AF3A4.BBF11452-ON852576FE.00430BF8-852576FE.0043D4E6@faa.gov (view raw or flat)
Thread:
Lists: pgsql-performance
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>

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







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

In response to

Responses

pgsql-performance by date

Next:From: Sabin CoandaDate: 2010-04-07 12:47:54
Subject: How check execution plan of a function
Previous:From: Yeb HavingaDate: 2010-04-07 07:18:52
Subject: Re: Some question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group