Re: SELECT performance drop

От: Laurenz Albe
Тема: Re: SELECT performance drop
Дата: ,
Msg-id: a42bbd33ff0c4fe6e8d6c805363f1e720330f941.camel@cybertec.at
(см: обсуждение, исходный текст)
Ответ на: Re: SELECT performance drop  (Jan Nielsen)
Список: pgsql-performance

Скрыть дерево обсуждения

SELECT performance drop  (Jan Nielsen, )
 Re: SELECT performance drop  (Jan Nielsen, )
  Re: SELECT performance drop  (legrand legrand, )
   Re: SELECT performance drop  (Jan Nielsen, )
    RE:SELECT performance drop  (legrand legrand, )
     Re: SELECT performance drop  (Jan Nielsen, )
      Re: SELECT performance drop  (Jan Nielsen, )
  Re: SELECT performance drop  (Jim Finnerty, )
   Re: SELECT performance drop  (Jan Nielsen, )
    Re: SELECT performance drop  (Laurenz Albe, )

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




В списке pgsql-performance по дате сообщения:

От: Laurenz Albe
Дата:
Сообщение: Re: SELECT performance drop
От: Mariel Cherkassky
Дата:
Сообщение: Re: ERROR: found xmin from before relfrozenxid