Date: Tue, 30 May 2006 19:38:17 -0500
From: "Jim C. Nasby" <jnasby@pervasive.com>
To: "wmiro@ig.com.br" <wmiro@ig.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why the 8.1 plan is worst than 7.4?
Message-ID: <20060531003817.GB59464@pervasive.com>
References: <20060526_120456_040065.wmiro@ig.com.br>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20060526_120456_040065.wmiro@ig.com.br>
User-Agent: Mutt/1.5.11

What's explain analyze show?

On Fri, May 26, 2006 at 09:04:56AM -0300, wmiro@ig.com.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@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461