Hi Saket
The first filter condition seems to be duplicated it appears this can be simplified from
and ( pdtaltrelt0_.status_typ_dbky=102
and ( pdtaltrelt0_.rule_status_typ_dbky is null )
or pdtaltrelt0_.status_typ_dbky in ( 19 )
or pdtaltrelt0_.status_typ_dbky in (20 )
)
and ( pdtaltrelt0_.status_typ_dbky in (19 , 20)
or pdtaltrelt0_.status_typ_dbky=102
and (pdtaltrelt0_.rule_status_typ_dbky is null)
)
TO
and (
(pdtaltrelt0_.status_typ_dbky = 102 and pdtaltrelt0_.rule_status_typ_dbky is null)
or pdtaltrelt0_.status_typ_dbky in (19, 20)
)
The Explain shows the filter seq filter like so
Filter: (
((status_typ_dbky = ANY ('{19,20}'::bigint[]))
OR ((status_typ_dbky = 102) AND (rule_status_typ_dbky IS NULL))
)
AND
(((status_typ_dbky = 102) AND (rule_status_typ_dbky IS NULL))
OR (status_typ_dbky = 19)
OR (status_typ_dbky = 20)
)
)
I can not see the difference between above/below the AND other than the order of operations...
Hi Postgres Experts,
Please help me on a query tuning.
Postgres verson: 11.5
This database has been migrated from oracle 12c to postgres. In Oracle query executes in 2-3 secs, but in postgres it hangs forever. There are no transactions at this time, I am stuck at first run after migration.
My analysis:
I have done vacuum full , analyze , even with 100% samples using a much higher value of default_statistics_target.
Also tried different hints using pg_hint_plan extension. Overall cost reduces, but actual run hangs forever.
I think problem is with correlated subquery join condition.
If "and pdtaltrelt%_.tenant_dkby=pdtaltrelt0_.tenant_dkby" condition is removed from both subqueries, result comes in secs(I understand that would be skipping correlated join)
SQL> select count(*) from pdtalt_rel_to_tenant_rel;
267216
SQL> select count(distinct tenant_dkby) from pdtalt_rel_to_tenant_rel;
3
Table DDLs , query plan and parameter configuration available at below git link:
I have 16 GB , 4 CPU , rhel 7 machine.
Thanks for help in advance, let me know if any additional information is required