I'm sorry for the bad example.
Here is another, with some data on PG: https://dbfiddle.uk/?rdbms=postgres_13&fiddle=ccfd1c4fa291e74a6db9db1772e2b5ac and Oracle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=21a98f499065ad4e2c35ff4bd1487e14.
I don't understand oracle's execution plan very well, but it doesn't seem to be hitting the Users table...
De: "Fabrízio de Royes Mello" <fabrizio@xxxxxxxxxxxxxx>
Para: "luis.roberto" <luis.roberto@xxxxxxxxxxxxxxx>
Cc: "pgsql-general" <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Enviadas: Sábado, 11 de julho de 2020 15:24:04
Assunto: Re: Join optimization
Para: "luis.roberto" <luis.roberto@xxxxxxxxxxxxxxx>
Cc: "pgsql-general" <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
Enviadas: Sábado, 11 de julho de 2020 15:24:04
Assunto: Re: Join optimization
Em sáb, 11 de jul de 2020 às 14:20, <luis.roberto@xxxxxxxxxxxxxxx> escreveu:
Hi!Recently on a blogpost [1] I saw that Oracle was able to "optimize" a join strategy by completely eliminating access to a table.Heres the execution on Oracle 18c [2] and PostgreSQL 13 (beta) [3].Is there a fundamental reason why PG can't do the same?
It does... did you see the “never executed” notice on the Postgres explain output?
Regards,
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
