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

út 30. 3. 2021 v 10:49 odesílatel Joel Jacobson <joel(at)compiler(dot)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
>