Re: Removing useless DISTINCT clauses

distinct_optimization_v6.patch
<http://www.postgresql-archive.org/file/t348990/distinct_optimization_v6.patch>  

Here is an update to this thread, for potential inclusion in v12.  I
couldn't get the most recent 'v5' patch to apply cleanly, so I recreated a
v6 patch on PG10.5 by hand, and made a few changes and improvements:

-  (a) If there is only one relation and the PK is present in the SELECT
list, then the distinctClause can be removed.  Thus, a query of the form
SELECT DISTINCT pk, * FROM t; recognizes that the body of the SELECT is
already distinct on pk, and doesn't do an unnecessary DISTINCT operation
-  (b) If there is only one relation, the PK is present, and there are no
aggregates, then the groupByClause can similarly be removed
-  (c) If DISTINCT ON is specified, but no ORDER BY is specified, then it
acts like a regular DISTINCT
-  (d) If the distinct clause is modified, this fact is recorded in a new
bool in the Query struct so that when the distinct was modified,
create_distinct_paths no longer Asserts that the needed pathkeys are
contained in the distinct_pathkeys.
-  (e) Since the new bool is in the Query struct,
copyfuncs/equalfuncs/outfuncs/readfuncs support is also provided.  This will
cause a problem unless the pg_rewrite catalog is regenerated.  

The pg_rewrite catalog contains a serialized representation of the Query
node in its ev_action column.  If there is a way to recreate the contents of
the pg_rewrite relation without bumping the catversion, can someone please
explain how?  If not, then this change is incomplete and would require a new
catalog version (catversion.h) too.

Additional work on this patch would be desirable.  It should check for
unique + not null, in addition to just the pk constraint.  The DISTINCT
could be eliminated in cases with multiple relations if all the joins are
1:1, although that would arguably belong in a different patch.

    /Jim

p.s. the v6 patch works for the problem case that Tom Lane reported with the
v5 patch




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html