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 <[email protected]> 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
>
>