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 <zzzzz(dot)graf(at)gmail(dot)com> 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(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
>>
>>