Re: MIN/MAX optimization for partitioned table

On Mon, Jul 20, 2009 at 7:40 PM, Alan Li<ali@truviso.com> wrote:
> Attached is an updated patch that removes the O(n^2) behavior and the
> awkwardness of optimizing the seqscan path as the plan was about to be
> created.  Now, the optimization is considered when appendrel is generating
> the paths.
>
> I also changed the plan as you had suggested.  It now looks like this:

Hm, that's not quite the plan I described either. I had in mind to
mirror the existing min/max optimization which put the whole thing in
an InitPlan and put a Result node in place of the actual plan. Your
original patch did that for each subplan but I thought it would be
better to do it for the whole aggregate.

However the more I think about it the more I don't understand why Tom
arranged to do that for the min/max optimization anyways. For
subqueries where that makes sense that would surely happen anyways
such as in the first example below. And for joins where it's necessary
the planner knows to put a Materialize node which sounds just as good. 

Here's what happens with your current patch in the case I was
concerned about -- note that the planner automatically detects the
case and turns the whole subplan into an initplan anyways:

postgres=# explain select * from y where j = (select min(i) from x) ;
                                         QUERY PLAN
----------------------------------------------------------------------------------------------
 Seq Scan on y  (cost=40.12..80.12 rows=12 width=4)
  Filter: (j = $0)
  InitPlan 1 (returns $0)
    ->  Aggregate  (cost=40.11..40.12 rows=1 width=4)
          ->  Append  (cost=0.00..34.10 rows=2403 width=4)
                ->  Limit  (cost=0.00..0.03 rows=1 width=4)
                      ->  Index Scan using xi on x  (cost=0.00..80.25
rows=2400 width=4)
                ->  Limit  (cost=0.00..0.03 rows=1 width=4)
                      ->  Index Scan using xi2 on x2 x
(cost=0.00..80.25 rows=2400 width=4)
                ->  Limit  (cost=0.00..0.03 rows=1 width=4)
                      ->  Index Scan using xi3 on x3 x
(cost=0.00..80.25 rows=2400 width=4)
                ->  Seq Scan on x1 x  (cost=0.00..34.00 rows=2400 width=4)
(12 rows)


And here's another case where you wouldn't want multiple execution --
but the planner here figures out to materialize the result:

postgres=# explain select * from y left outer join (select min(i) as i
from x) as x on (i=j);
                                           QUERY PLAN
--------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=40.13..128.13 rows=2400 width=8)
  Join Filter: ((min(public.x.i)) = y.j)
  ->  Seq Scan on y  (cost=0.00..34.00 rows=2400 width=4)
  ->  Materialize  (cost=40.13..40.14 rows=1 width=4)
        ->  Aggregate  (cost=40.11..40.12 rows=1 width=4)
              ->  Append  (cost=0.00..34.10 rows=2403 width=4)
                    ->  Limit  (cost=0.00..0.03 rows=1 width=4)
                          ->  Index Scan using xi on x
(cost=0.00..80.25 rows=2400 width=4)
                    ->  Limit  (cost=0.00..0.03 rows=1 width=4)
                          ->  Index Scan using xi2 on x2 x
(cost=0.00..80.25 rows=2400 width=4)
                    ->  Limit  (cost=0.00..0.03 rows=1 width=4)
                          ->  Index Scan using xi3 on x3 x
(cost=0.00..80.25 rows=2400 width=4)
                    ->  Seq Scan on x1 x  (cost=0.00..34.00 rows=2400 width=4)
(13 rows)


So I'm a bit puzzled why Tom's min/max optimization bothers with the
whole Initplan/Result business itself anyways.

--

greg
http://mit.edu/~gsstark/resume.pdf