Re: Parameterized aggregate subquery (was: Pull up aggregate subquery)

On 2011-07-02 10:02, Hitoshi Harada wrote:
>
>> Although I still need to think about suitable regression test case,
>> the patch itself can be reviewed again. You may want to try some
>> additional tests as you imagine after finding my test case gets
>> quicker.

Hello Hitoshi-san,

I took a look at your latest patch and it looks good, no comments.
However I also tried it against current 9.2 HEAD and the test query of
the start of this thread.

Before and after applying the patch, I get the same result for the test
query.

postgres=# explain select m_id, sum_len from size_m m inner join(select
m_id,
sum(length(val)) as sum_len from size_l group by m_id)l on m.id =
l.m_id where val = '10101';
QUERY PLAN
----------------------------------------------------------------------------------
Nested Loop (cost=79392.64..82938.05 rows=100 width=12)
Join Filter: (m.id = size_l.m_id)
-> Seq Scan on size_m m (cost=0.00..897.00 rows=1 width=4)
Filter: (val = '10101'::text)
-> GroupAggregate (cost=79392.64..81592.15 rows=19951 width=277)
-> Sort (cost=79392.64..79892.64 rows=200000 width=277)
Sort Key: size_l.m_id
-> Seq Scan on size_l (cost=0.00..9829.00 rows=200000
width=277)

I double checked that I had applied the patch (git diff shows the
patch), installed and restarted postgres. The database is a fresh
created database with no edits in postgresql.conf.

regards,

--
Yeb Havinga
http://www.mgrid.net/
Mastering Medical Data