Re: Query with correlated join having slow performance

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

On Mon, Dec 9, 2019 at 1:33 PM saket bansal <saket(dot)tcs(at)gmail(dot)com> wrote:

> 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:
> https://github.com/bansalsaket/PG_correlated_subquery_slowness
>
> I have 16 GB , 4 CPU , rhel 7 machine.
>
> Thanks for help in advance, let me know if any additional information is
> required
>
>