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

Поиск
Список
Период
Сортировка
От Pavel Stehule
Тема Re: Idea: Avoid JOINs by using path expressions to follow FKs
Дата
Msg-id CAFj8pRC0AgfQsO4OuimY=4LQAB_1pB1k5OCZqSYqjDFmNySfEw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Idea: Avoid JOINs by using path expressions to follow FKs  ("Joel Jacobson" <joel@compiler.org>)
Список pgsql-hackers


út 30. 3. 2021 v 10:49 odesílatel Joel Jacobson <joel@compiler.org> napsal:
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?


ok, this is not EAV.



/Joel

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

Предыдущее
От: Peter Smith
Дата:
Сообщение: Use consistent terminology for tablesync slots.
Следующее
От: Amit Kapila
Дата:
Сообщение: Re: [PATCH] add concurrent_abort callback for output plugin