Horton

Re: Removing unneeded self joins

Laurenz Albe <laurenz.albe@cybertec.at>

View thread
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Andrey Lepikhov <a.lepikhov@postgrespro.ru>, Hywel Carver <hywel@skillerwhale.com>, pgsql-hackers@lists.postgresql.org
Cc: Konstantin Knizhnik <knizhnik@garret.ru>
Date: 2021-06-30T15:55:21Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Remove GUC_NOT_IN_SAMPLE from enable_self_join_elimination

  2. Put enable_self_join_elimination into postgresql.conf.sample

  3. Get rid of ojrelid local variable in remove_rel_from_query()

  4. Implement Self-Join Elimination

  5. Revert: Remove useless self-joins

  6. Replace lateral references to removed rels in subqueries

  7. Replace relids in lateral subquery parse tree during SJE

  8. Forbid SJE with result relation

  9. Fix misuse of RelOptInfo.unique_for_rels cache by SJE

  10. Replace the relid in some missing fields during SJE

  11. Revert 56-bit relfilenode change and follow-up commits.

  12. Stabilize timetz test across DST transitions.

  13. Speed up finding EquivalenceClasses for a given set of rels

  14. Fix mark-and-restore-skipping test case to not be a self-join.

On Wed, 2021-06-30 at 14:21 +0300, Andrey Lepikhov wrote:
> I think, here we could ask more general question: do we want to remove a 
> 'IS NOT NULL' clause from the clause list if the rest of the list 
> implicitly implies it?
> 
> EXPLAIN (ANALYZE, VERBOSE) 
> SELECT * 
> FROM a WHERE (X IS NOT NULL) AND (X IS NULL);
>                                               QUERY PLAN 
> 
> ----------------------------------------------------------------------------------------------------
>   Seq Scan on public.a  (cost=0.00..15.00 rows=87 width=4) (actual time=0.136..0.136 rows=0 loops=1)
>     Output: x
>     Filter: ((a.x IS NOT NULL) AND (a.x IS NULL))
>     Rows Removed by Filter: 1000
> 
> It could reduce a number of selectivity mistakes, but increase CPU 
> consumption.
> If we had such a clause analyzing machinery, we could trivially remove 
> this unneeded qual.

On the other hand, sometimes something like that can be used to change
the optimizer's estimates to encourage certain plans.

We also don't optimize "ORDER BY x + 0" (which now you can use to prevent
an index scan) or the famous OFFSET 0, partly because it saves planning time,
partly because those can be useful tools.

Generally I have the impression that we are not too keen on spending
planning time on optimizing cases that can be trivially improved by rewriting
the query.

Yours,
Laurenz Albe