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