Re: Query with correlated join having slow performance

[saket bansal <saket.tcs@xxxxxxxxx>]

Thanks Justin for pointing this out.

 More work for optimizer for nothing, I will remove it.

On Mon, Dec 9, 2019 at 2:48 PM Justin <zzzzz.graf@xxxxxxxxx> wrote:

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.tcs@xxxxxxxxx> 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