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

ú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
>