To: pgsql-performance@postgresql.org
From: wmiro@ig.com.br <wmiro@ig.com.br>
Subject: Why the 8.1 plan is worst than 7.4?
Date: Fri, 26 May 2006 09:04:56 -0300
Message-ID: <20060526_120456_040065.wmiro@ig.com.br>
Teste: asaes
MIME-Version: 1.0
Content-Description: Mail message body
Content-Type: text/plain; charset=ISO-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit

Hi, 

I have 2 servers, one of them has a 7.4 postgres and the other has a 8.1 

I have this query: 

select fagrempr,fagrdocr,fagrserr,fagrparr 
from arqcfat 
left join arqfagr on fagrorig = 'CFAT' and fagrdocu = cfatdocu and fagrempe 
= cfatempe and fagrseri = cfatseri 
where cfatdata between '2006-01-01' and '2006-01-31' 
and cfattipo = 'VD' 
and cfatstat <> 'C' 
and fagrform = 'CT' 
and fagrtipr = 'REC' 
group by fagrempr,fagrdocr,fagrserr,fagrparr 

The 8.1 give me this plan: 

 HashAggregate  (cost=59.07..59.08 rows=1 width=20) 
   ->  Nested Loop  (cost=0.00..59.06 rows=1 width=20) 
         ->  Index Scan using arqfagr_arqfa3_key on arqfagr  
(cost=0.00..53.01 rows=1 width=36) 
               Index Cond: ((fagrorig = 'CFAT'::bpchar) AND (fagrform = 
'CT'::bpchar)) 
               Filter: (fagrtipr = 'REC'::bpchar) 
         ->  Index Scan using arqcfat_arqcfat1_key on arqcfat  
(cost=0.00..6.03 rows=1 width=16) 
               Index Cond: (("outer".fagrempe = arqcfat.cfatempe) AND 
("outer".fagrdocu = arqcfat.cfatdocu) AND ("outer".fagrseri = 
arqcfat.cfatseri)) 
               Filter: ((cfatdata >= '01-01-2006'::date) AND (cfatdata <= 
'31-01-2006'::date) AND (cfattipo = 'VD'::bpchar) AND (cfatstat <> 
'C'::bpchar)) 

The 7.4 give me this plan: 

HashAggregate  (cost=2163.93..2163.93 rows=1 width=19) 
   ->  Nested Loop  (cost=0.00..2163.92 rows=1 width=19) 
         ->  Index Scan using arqcfat_arqcfat2_key on arqcfat  
(cost=0.00..2145.78 rows=3 width=15) 
               Index Cond: ((cfatdata >= '01-01-2006'::date) AND (cfatdata 
<= '31-01-2006'::date)) 
               Filter: ((cfattipo = 'VD'::bpchar) AND (cfatstat <> 
'C'::bpchar)) 
         ->  Index Scan using arqfagr_arqfa1_key on arqfagr  
(cost=0.00..6.03 rows=1 width=34) 
               Index Cond: ((arqfagr.fagrorig = 'CFAT'::bpchar) AND 
(arqfagr.fagrempe = "outer".cfatempe) AND (arqfagr.fagrdocu = 
"outer".cfatdocu) AND (arqfagr.fagrseri = "outer".cfatseri)) 
               Filter: ((fagrform = 'CT'::bpchar) AND (fagrtipr = 
'REC'::bpchar)) 

Why the plan is worst in postgres 8.1? 

I know the best plan is read fisrt the table which has a date index as the 
7.4 did, because in a few days I will have few lines too, so the query will 
be faster. 

Is there some thing I have to change in 8.1 to make the plans as the 7.4? 

Thanks , 

Waldomiro C. Neto.