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.05rows=100 width=12)   Join Filter: (m.id = size_l.m_id)   ->  Seq Scan on size_m m
(cost=0.00..897.00rows=1 width=4)         Filter: (val = '10101'::text)   ->  GroupAggregate  (cost=79392.64..81592.15
rows=19951width=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