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

Поиск
Список
Период
Сортировка
От Joel Jacobson
Тема Re: Idea: Avoid JOINs by using path expressions to follow FKs
Дата
Msg-id 9db6d619-522f-457b-8f28-3d7730c6a26d@www.fastmail.com
обсуждение исходный текст
Ответ на Re: Idea: Avoid JOINs by using path expressions to follow FKs  (Pavel Stehule <pavel.stehule@gmail.com>)
Ответы Re: Idea: Avoid JOINs by using path expressions to follow FKs
Список pgsql-hackers
Дерево обсуждения
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

В списке pgsql-hackers по дате отправления: