Обсуждение: Why the 8.1 plan is worst than 7.4?

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.

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

"wmiro@ig.com.br" <wmiro@ig.com.br> writes:
> Why the plan is worst in postgres 8.1?

(1) you have not actually shown us that the plan is worse.  If you are
complaining that the planner is wrong, EXPLAIN output (which contains
only the planner's estimates) is useless for proving your point.  Show
EXPLAIN ANALYZE.

(2) Have you ANALYZEd these tables recently in either database?  The
discrepancies in estimated rowcounts suggest that the two planners
are working with different statistics.

            regards, tom lane