Re: Removing useless DISTINCT clauses

From: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
To: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Cc: Melanie Plageman <melanieplageman(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Removing useless DISTINCT clauses
Date: 2018-03-23 07:00:39
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

David Rowley wrote:
> > Would it be very difficult to extend that to "if any unique constraints are
> > contained in the DISTINCT clause"?
> Unfortunately, it's quite a bit more work to make that happen. It's
> not just unique constraints that are required to make this work. We
> also need to pay attention to NOT NULL constraints too, as we're
> unable to prove function dependency when the keys have NULLs, as there
> may be any number of rows containing NULL values.
> The problem is that in order to properly invalidate cached plans we
> must record the constraint OIDs which the plan depends on. We can do
> that for PK and UNIQUE constraints, unfortunately, we can't do it for
> NOT NULL constraints as, at the moment, these are just properties of
> pg_attribute. For this to be made to work we'd need to store those in
> pg_constraint too, which is more work that I'm going to do for this
> patch.

That makes sense; thanks for explaining.

Laurenz Albe

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2018-03-23 07:27:01 Re: [HACKERS] MERGE SQL Statement for PG11
Previous Message Pavel Stehule 2018-03-23 05:37:58 Re: [HACKERS] proposal: schema variables