Re: Dynamic Partitioning using Segment Visibility Maps

On Thu, 2008-01-10 at 21:43 +0100, Gavin Sherry wrote:
> On Thu, Jan 10, 2008 at 07:25:00AM +0000, Simon Riggs wrote:
> > On Thu, 2008-01-10 at 03:06 +0100, Gavin Sherry wrote:
> > > If the exclusion is executor driven, the planner cannot help but
> > > create a seq scan plan. The planner will think you're returning 100X
> > > rows when really you end up returning X rows. After that, all
> > > decisions made by the planner are totally bogus.
> >
> > One of the most important queries on large tables is handling
> > WHERE Eventdate = CURRENT DATE;
>
> Really? Well, this isn't handled by the current partitioning approach

Yes, exactly why we need to fix it and why I'm mentioning it here.

> > We cannot perform partition exclusion using this type of WHERE clause at
> > planning time because the CURRENT DATE function is STABLE.
>
> We can do the exact same thing -- if it's a direction people want to
> take. In fact, we can do it better/faster because once we've evaluated one
> partition we know that there are no others to evaluate.

Lost you completely here. I'm explaining to you that *nobody* can solve
those problems solely at planning time, by definition, so it has to be
done at execution time. I'm not saying anything about your way, my way.

> > So it seems clear that we need to make partition exclusion work at
> > executor time, whatever else we do.
>
> One example doesn't make the rule. Most people doing range partitioning
> are going to be doing either specific dates or date ranges -- i.e.,
> constants or things that can be folded to constants by the planner. At
> least, that's what I've seen.

It's not always true that planning time = execution time.

Using CURRENT DATE to access current day, week, month is common in many
applications, as is parameterised SQL for higher transaction rate apps.

We we can't re-write all the SQL, so we need to make it work.

I think if you demand a full function implementation of partitioning,
you'd better take into account *all* of the requirements.

--
Simon Riggs
2ndQuadrant http://www.2ndQuadrant.com