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.
