Re: Idea: Avoid JOINs by using path expressions to follow FKs

On Tue, Mar 30, 2021, at 10:24, Pavel Stehule wrote:
>
>>
>> I think this is a good example of where this improves the situation the most,
>> when you have multiple joins of the same table, forcing you to come up with multiple aliases
>> for the same table, keeping them all in memory while writing and reading such queries.
>
> ...
> I remember multiple self joins only when developers used an EAV model. This is an antipattern, and today we have better tools, and we don't need it. It is scary, because it is completely against the relational model.

No, you are mistaken. There are no self-joins in any of the examples I presented.
I merely joined in the same table multiple times, but not with itself, so it's not a self join.

Here is the query again, it doesn't contain any self-joins:

SELECT
format
(
'ALTER TABLE %I.%I RENAME CONSTRAINT %I TO %I;',
conrel_nsp.nspname,
conrel.relname,
pg_constraint.conname,
confrel.relname
) AS sql_cmd,
COUNT(*) OVER (PARTITION BY pg_constraint.conrelid, pg_constraint.confrelid)
AS count_foreign_keys_to_same_table
FROM pg_constraint
JOIN pg_class AS conrel
ON conrel.oid = pg_constraint.conrelid
JOIN pg_class AS confrel
ON confrel.oid = pg_constraint.confrelid
JOIN pg_namespace AS conrel_nsp
ON conrel_nsp.oid = conrel.relnamespace
WHERE pg_constraint.contype = 'f'

Where would the antipattern be here?

/Joel