Re: Feedback about Drupal SQL debugging

2009/8/22 Stephen Frost <sfrost(at)snowman(dot)net>:
>> This is a bit trickier than it looks because it makes the validity of a
>> query dependent on the existence of an appropriate uniqueness
>> constraint; thus for example DROP CONSTRAINT might invalidate a stored
>> rule or view.  See prior discussions.
>
> Ah, yes.  Couldn't the dependency system be used to handle this though?
> If you try to drop that constraint it'll complain unless you use cascade
> which would drop the view?  I'll try and find older discussions.  Sadly,
> I don't see it on the TODO.  Perhaps I can add it.

All this wasn't possible before 8.3 so there are a whole slew of
optimizations that have been kind of waiting in the wings until we got
that infrastructure.

The first step is probably to do the opposite of what we're talking
about here: cases where people *have* added extra columns to the GROUP
BY key so they can use those columns in their select list. We can
remove those columns from the sort or hash comparison key if there's a
column (or columns) which is a unique constraint key for the same
source. Similarly we can remove columns from an ORDER BY if the order
key has earlier columns which are already a unique key for the same
source.

That would be fairly simple and it would provide a good test case for
the dependency tracking stuff and plan invalidation triggered by
constraint ddl. It wouldn't be a massive performance change but it
would help some cases where the sort node is comparing a lot of
redundant keys.

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