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

Re: Why the 8.1 plan is worst than 7.4?

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: "wmiro(at)ig(dot)com(dot)br" <wmiro(at)ig(dot)com(dot)br>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Why the 8.1 plan is worst than 7.4?
Date: 2006-05-31 00:38:17
Message-ID: 20060531003817.GB59464@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2006-05-31 01:07:34
Subject: Re: Query performance
Previous:From: D'Arcy J.M. CainDate: 2006-05-30 23:05:08
Subject: Re: INSERT OU UPDATE WITHOUT SELECT?

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