On 24/6/2022 18:58, Leif Harald Karlsen wrote:
I have a made a small deductive database on top of PostgreSQL for
educational/research purposes. In this setting, due to certain
VIEW-constructions, queries often end up being self-joins on primary
keys, e.g.:
SELECT t1.id, t2.val
FROM t AS t1 JOIN t AS t2 USING (id);
where t(id) is a primary key. This query is equivalent to the much more
efficient:
SELECT id, val FROM t AS t1;
However, PostgreSQL currently does not seem to implement this
simplification. Therefore, I have looked into writing an extension that
performs this, but I am struggling a bit with finding out when this
simplification should be done, i.e. which hook I should implement.
It is true, but you can use a proposed patch that adds such
functionality [1].
I tried to reproduce your case:
CREATE TABLE t(id int PRIMARY KEY, val text);
explain verbose
SELECT t1.id, t2.val FROM t AS t1 JOIN t AS t2 USING (id);
With this patch you will get a plan:
Seq Scan on public.t t2
Output: t2.id, t2.val
Filter: (t2.id IS NOT NULL)
The approach, implemented in this patch looks better because removes
self-joins on earlier stage than the path generation stage. Feel free to
use it in your research.
[1]
https://www.postgresql.org/message-id/a1d6290c-44e0-0dfc-3fca-66a68b310...@postgrespro.ru
--
regards,
Andrey Lepikhov
Postgres Professional
