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