Re: SELECT performance drop

On Wed, Jan 23, 2019 at 6:51 AM Jim Finnerty <jfinnert(at)amazon(dot)com> wrote:

> One thing that isn't helping is that you have a redundant predicate. The
> selectivity of this predicate is also estimated too low, so removing the
> redundant predicate might improve the estimate and change the plan:
>
> ( "
> + " o.consumer IS NULL "
> + " ) OR ( "
> + " o.consumer IS NOT NULL "
> + " AND o.consumer > 0
>
> remove "o.consumer IS NOT NULL AND", which is implied by o.consumer > 0.
> This predicate should have been automatically removed, but the filter shown
> in depesz shows that it was not.
>

Good point -- the new generated SQL is

select
order0_.id as id1_7_,
order0_.created_by as created_2_7_,
order0_.created_date as created_3_7_,
order0_.last_modified_by as last_mod4_7_,
order0_.last_modified_date as last_mod5_7_,
order0_.consumer as consumer6_7_,
order0_.market_id as market_14_7_,
order0_.original as original7_7_,
order0_.owner_id as owner_i15_7_,
order0_.owner_target as owner_ta8_7_,
order0_.price as price9_7_,
order0_.session_id as session16_7_,
order0_.side as side10_7_,
order0_.supplier as supplie11_7_,
order0_.type as type12_7_,
order0_.units as units13_7_
from
fm_order order0_ cross
join
fm_session session1_
where
order0_.session_id=session1_.id
and order0_.type='LIMIT'
and session1_.original=7569
and (
order0_.consumer is null
or order0_.consumer>0
and (
exists (
select
1
from
fm_order order2_ cross
join
fm_session session3_
where
order2_.session_id=session3_.id
and order2_.id=order0_.consumer
and session3_.original=7569
and order2_.type='LIMIT'
and order2_.owner_id<>order0_.owner_id
)
)
)
order by
order0_.last_modified_date DESC;

> If you can find out what the faster plan was, that would be helpful to
> know.
>

which results in:

https://explain.depesz.com/s/vGVo

>
>
>
> -----
> Jim Finnerty, AWS, Amazon Aurora PostgreSQL
> --
> Sent from:
> http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
>
>