Re: MIN/MAX optimization for partitioned table

On Sat, Jul 18, 2009 at 3:13 AM, Greg Stark <gsstark(at)mit(dot)edu> wrote:

> This part:
>
> ! /* only try to optimize children rel's */
> ! foreach (lc, root->append_rel_list)
> ! {
> ! AppendRelInfo *a = (AppendRelInfo *) lfirst(lc);
> !
> ! if (a->child_relid == childrel->relid &&
> ! a->parent_relid == parentrel->relid)
> ! {
> ! appinfo = a;
> ! break;
> ! }
> ! }
>
> Looks like O(n^2). I guess that's a bigger problem than with just this
> patch. Perhaps append_rel_list should be a dynahash in general. I
> never really understood why it was simpler to have a single global
> append_rel_list anyways.
>

Yeah, are you running into the same issue as well? I tried to figure out a
way around the O(n^2) behavior, but there were no existing direct
relationship between the child subpath and its associated AppendRelInfo. I
think an AppenRelInfo dynahash would work and just have it hash by the
child_relid.

>
> The other thing is it would be nice if we could avoid making separate
> subplans for each child and instead make one for the whole structure
> including the aggregate. It would at the very least make the explain
> output prettier, but I think it would avoid repeated execution of the
> aggregate in some cases as well.

How would this plan look? I think the repeated execution of the aggregate
comes from having to process the output of each child. So it's O(n)
executions where n is the number of children, assuming each child has the
appropriate index for this optimization.

The other optimization that I thought was that the optimizer might use the
check constraints (in the range partitioning) on the column that I want to
do a MIN/MAX on. Then it'll only have to execute the subplan in one child
partition and the parent partition. But it was more of a wild idea on my
part, not sure if that's possible.

>
>
> --
> greg
> http://mit.edu/~gsstark/resume.pdf <http://mit.edu/%7Egsstark/resume.pdf>
>
>
>