Re: Query with correlated join having slow performance

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