Re: partitionning

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Mike Rylander <mrylander(at)gmail(dot)com>, Christian Kratzer <ck(at)cksoft(dot)de>, pgsql-general(at)postgresql(dot)org
Subject: Re: partitionning
Date: 2005-03-13 14:14:01
Message-ID: 20050313141355.GA13058@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sat, Mar 12, 2005 at 11:35:51PM -0500, Tom Lane wrote:
> The issue isn't really whether the planner *could* use a constraint
> to decide that a table need not be scanned at all. As you say, we
> have practically all the infrastructure needed for such inferences.
> To me the real reason why we don't do that is the lack of a mechanism
> to force the plan to be reconsidered if the constraint is dropped.
> Without the constraint, the plan will still run, but it may deliver
> wrong answers. (The closest current equivalent is the possibility of
> dropping an index that a plan depends on --- but at least we will fail
> outright if the index isn't there anymore.)

Wow, now that's a corner case I hadn't though of. Actually, it seems to
me a similar thing may happen if you have a query on a table and you
create a new inherited table from that. Is the new table included in
this old plan? There are any number of DDL statements that can affect
planning. All of them I guess...

> In short, I won't accept any such patch until after we build a mechanism
> for invalidating cached plans. Which we need anyway for other reasons.
> So the path ahead is clear, in my mind anyway ... but this is not the
> next step to take.

People still keep running into the invalid plan issue, consider
temporary tables. Would if be feasable to have a plan maintain a list
of objects it depends on, every column, table, constraint, domain,
function, type, etc referred to and regenerate the plan if any of them
change. This list could become huge for any moderatly complicated query
and there doesn't seem to be a lot of gain most of the time. But it
would be needed for completeness.

Actually, it would probably be enough to just list tables and columns
and have changes in constraints, triggers and default values be changes
on the columns they refer to.

I'm sure this has been discussed to death already though and is just
waiting for a suitable implementation.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Ryan J. Cavicchioni 2005-03-13 18:28:33 Permission's question
Previous Message Miroslav Šulc 2005-03-13 14:03:14 Re: General query optimization howto