Re: Why the 8.1 plan is worst than 7.4?

What's explain analyze show?

On Fri, May 26, 2006 at 09:04:56AM -0300, wmiro(at)ig(dot)com(dot)br wrote:
> 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.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461