Re: Aggregate function with subquery in 8.3 and 8.4.

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Aggregate function with subquery in 8.3 and 8.4.
Date: 2009-09-01 09:05:44
Message-ID: 20090901090544.GH5407@samason.me.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Mon, Aug 31, 2009 at 04:02:43PM -0700, Sheng Cheng wrote:
> I though the following query would give me the same results in 8.4.0 and
> 8.3.1.

It should give the same results! This looks like a bug in 8.4 to me, in
an attempt to optimize things it's pulling the CASE out from inside the
inner select and this is changing the semantics of the query. Doing an
EXPLAIN in 8.4 gives the following:

GroupAggregate (cost=181.86..387.73 rows=200 width=64)
-> Merge Left Join (cost=181.86..341.83 rows=8580 width=64)
Merge Cond: (t1.f1 = (CASE WHEN (t2.f1 = '111'::text) THEN '111'::text
ELSE t2.f1 END))
-> Sort (cost=90.93..94.20 rows=1310 width=32)
Sort Key: t1.f1
-> Seq Scan on t1 (cost=0.00..23.10 rows=1310 width=32)
-> Sort (cost=90.93..94.20 rows=1310 width=64)
Sort Key: (CASE WHEN (t2.f1 = '111'::text) THEN '111'::text ELSE
t2.f1 END)
-> Seq Scan on t2 (cost=0.00..23.10 rows=1310 width=64)

While in 8.3 I get:

GroupAggregate (cost=198.23..378.88 rows=200 width=64)
-> Merge Left Join (cost=198.23..333.48 rows=8580 width=64)
Merge Cond: (t1.f1 = ts.f1)
-> Sort (cost=90.93..94.20 rows=1310 width=32)
Sort Key: t1.f1
-> Seq Scan on t1 (cost=0.00..23.10 rows=1310 width=32)
-> Sort (cost=107.30..110.58 rows=1310 width=64)
Sort Key: ts.f1
-> Subquery Scan ts (cost=0.00..39.48 rows=1310 width=64)
-> Seq Scan on t2 (cost=0.00..26.38 rows=1310 width=32)

Notice that the "Merge Cond" is working on the CASE expression in 8.4.
This is too late and is breaking things.

--
Sam http://samason.me.uk/

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sam Mason 2009-09-01 09:38:22 Re: Aggregate function with subquery in 8.3 and 8.4.
Previous Message Glyn Astill 2009-09-01 08:20:50 Re: Eclipse jdbc postgresql