Обсуждение: SELECT performance drop

Hello,
could you check that statistics for fm_session are accurate ?

Regards
PAscal




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

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.

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



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

Hi,
is there an index on
 fm_order(session_id,type)
?

regards
PAscal

On Wed, 2019-01-23 at 10:28 -0700, Jan Nielsen wrote:
>     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;

It might be more efficient to rewrite that along these lines:

SELECT DISTINCT order0_.*
FROM fm_order order0_
   JOIN fm_session session1_ ON order0_.session_id = session1_.id
   LEFT JOIN fm_order order2_ ON order2_.id = order0_.consumer
   LEFT JOIN fm_session session3_ ON order2_.session_id = session3_.id
WHERE coalesce(order2_.id, 1) > 0
AND /* all the other conditions */;

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com