0

Given this view

CREATE OR REPLACE VIEW public."SellMeta"
AS WITH wh AS (
         SELECT "WWHH".id,
            "WWHH".url,
            "WWHH".name,
            "WWHH".ttt
           FROM "WWHH"
        ), fj AS (
         SELECT "FFF".id,
            "FFF".aaa,
            "FFF".mmm,
            "FFF".bbb,
            "FFF"."ccc",
            "FFF"."iId"
           FROM "FFF"
        )
 SELECT i.id,
    i."pId",
    i.rrr,
    i."ccc",
    i.eee,
    i.ttt,
    fjj."wwwList",
    to_jsonb(wh.*) AS wh2,
    i.vat
   FROM "Sell" i
     LEFT JOIN LATERAL ( SELECT jsonb_agg(to_jsonb(fj.*) #- '{iId}'::text[] ORDER BY fj.aaa DESC, fj.mmm DESC, fj.bbb DESC) AS "wwwList",
            1 AS k
           FROM fj
          WHERE fj."iId" = i.id
          GROUP BY 1::integer) fjj ON true
     LEFT JOIN wh ON i."whId" = wh.id;

This query is very slow:

select
    "SellMeta" .* as "i",
    case
        when l.id is null then null
        else jsonb_bbb_object('id', l."id", 'code', l."code", 'kind', l."kind")
    end as "licence"
from
    "SellMeta"
left join "Licence" as "l" on
    "l"."id" = "SellMeta"."pId"
order by
    "ccc" desc,
    "id" desc
limit 25

This is the relative plan

Limit  (cost=28088832.32..28088832.38 rows=25 width=669) (actual time=8391.716..8391.720 rows=25 loops=1)                                                                       
  ->  Sort  (cost=28088832.32..28089220.98 rows=155463 width=669) (actual time=8232.079..8232.082 rows=25 loops=1)                                                              
        Sort Key: i."ccc" DESC, i.id DESC                                                                                                                                 
        Sort Method: top-N heapsort  Memory: 49kB                                                                                                                               
        ->  Nested Loop Left Join  (cost=5.06..28084445.27 rows=155463 width=669) (actual time=0.117..8171.565 rows=155463 loops=1)                                             
              ->  Nested Loop Left Join  (cost=4.78..28034018.83 rows=155463 width=637) (actual time=0.100..7698.774 rows=155463 loops=1)                                       
                    Join Filter: (i."whId" = "WWHH".id)                                                                                                                 
                    ->  Nested Loop Left Join  (cost=4.78..27894379.09 rows=155463 width=621) (actual time=0.095..7667.807 rows=155463 loops=1)                                 
                          ->  Seq Scan on "Sell" i  (cost=0.00..3321.63 rows=155463 width=589) (actual time=0.018..21.796 rows=155463 loops=1)                              
                          ->  GroupAggregate  (cost=4.78..179.39 rows=1 width=36) (actual time=0.049..0.049 rows=1 loops=155463)                                                
                                Group Key: 1                                                                                                                                    
                                ->  Bitmap Heap Scan on "FFF"  (cost=4.78..178.92 rows=45 width=56) (actual time=0.004..0.009 rows=11 loops=155463)                 
                                      Recheck Cond: ("iId" = i.id)                                                                                                       
                                      Heap Blocks: exact=1746805                                                                                                                
                                      ->  Bitmap Index Scan on aaajournal_instanceid_idx  (cost=0.00..4.77 rows=45 width=0) (actual time=0.002..0.002 rows=11 loops=155463)
                                            Index Cond: ("iId" = i.id)                                                                                                   
                    ->  Materialize  (cost=0.00..10.90 rows=60 width=48) (actual time=0.000..0.000 rows=0 loops=155463)                                                         
                          ->  Seq Scan on "WWHH"  (cost=0.00..10.60 rows=60 width=48) (actual time=0.002..0.002 rows=0 loops=1)                                              
              ->  Index Scan using "Licence_pkey" on "Licence" l  (cost=0.29..0.31 rows=1 width=50) (actual time=0.001..0.001 rows=1 loops=155463)                              
                    Index Cond: (id = i."pId")                                                                                                                            
Planning Time: 0.860 ms                                                                                                                                                         
JIT:                                                                                                                                                                            
  Functions: 23                                                                                                                                                                 
  Options: Inlining true, Optimization true, Expressions true, Deforming true                                                                                                   
  Timing: Generation 2.218 ms, Inlining 20.417 ms, Optimization 80.113 ms, Emission 59.120 ms, Total 161.867 ms                                                                 
Execution Time: 8394.096 ms

But if i refactor replacing the VIEW using WITH

with wh as (
select
    "WWHH".id,
    "WWHH".url,
    "WWHH".name,
    "WWHH".ttt
from
    "WWHH"
        ),
fj as (
select
    "FFF".id,
    "FFF".aaa,
    "FFF".mmm,
    "FFF".bbb,
    "FFF"."ccc",
    "FFF"."iId"
from
    "FFF"
        ),
 "SellMeta" as (
select
    i.id,
    i."pId",
    i.rrr,
    i."ccc",
    i.eee,
    i.ttt,
    fjj."wwwList",
    to_jsonb(wh.*) as wh2,
    i.vat
from
    "Sell" i
left join lateral (
    select
        jsonb_agg(to_jsonb(fj.*) #- '{iId}'::text[] order by fj.aaa desc, fj.mmm desc, fj.bbb desc) as "wwwList",
        1 as k
    from
        fj
    where
        fj."iId" = i.id
    group by
        1::integer) fjj on
    true
left join wh on
    i."whId" = wh.id)
select
    "SellMeta" .* as "i",
    case
        when l.id is null then null
        else jsonb_bbb_object('id', l."id", 'code', l."code", 'kind', l."kind")
    end as "licence"
from
    "SellMeta"
left join "Licence" as "l" on
    "l"."id" = "SellMeta"."pId"
order by
    "ccc" desc,
    "id" desc
limit 25

The query is much faster

This is the plan

Limit  (cost=5.48..4522.91 rows=25 width=669) (actual time=0.287..1.161 rows=25 loops=1)                                                                             
  ->  Nested Loop Left Join  (cost=5.48..28091742.90 rows=155463 width=669) (actual time=0.285..1.154 rows=25 loops=1)                                               
        ->  Nested Loop Left Join  (cost=5.20..28042482.43 rows=155463 width=637) (actual time=0.229..0.798 rows=25 loops=1)                                         
              Join Filter: (i."whId" = "WWHH".id)                                                                                                            
              ->  Nested Loop Left Join  (cost=5.20..27903231.35 rows=155463 width=621) (actual time=0.219..0.774 rows=25 loops=1)                                   
                    ->  Index Scan using created_sort_idx on "Sell" i  (cost=0.42..12173.89 rows=155463 width=589) (actual time=0.080..0.106 rows=25 loops=1)    
                    ->  GroupAggregate  (cost=4.78..179.39 rows=1 width=36) (actual time=0.025..0.025 rows=1 loops=25)                                               
                          Group Key: 1                                                                                                                               
                          ->  Bitmap Heap Scan on "FFF"  (cost=4.78..178.92 rows=45 width=56) (actual time=0.010..0.010 rows=1 loops=25)                 
                                Recheck Cond: ("iId" = i.id)                                                                                                  
                                Heap Blocks: exact=21                                                                                                                
                                ->  Bitmap Index Scan on aaajournal_instanceid_idx  (cost=0.00..4.77 rows=45 width=0) (actual time=0.008..0.008 rows=1 loops=25)
                                      Index Cond: ("iId" = i.id)                                                                                              
              ->  Materialize  (cost=0.00..10.90 rows=60 width=48) (actual time=0.000..0.000 rows=0 loops=25)                                                        
                    ->  Seq Scan on "WWHH"  (cost=0.00..10.60 rows=60 width=48) (actual time=0.005..0.005 rows=0 loops=1)                                         
        ->  Index Scan using "Licence_pkey" on "Licence" l  (cost=0.29..0.31 rows=1 width=50) (actual time=0.009..0.009 rows=1 loops=25)                             
              Index Cond: (id = i."pId")                                                                                                                       
Planning Time: 1.549 ms                                                                                                                                              
Execution Time: 1.280 ms

Why using the View instead of an inline WITH has a so great performance impact?

I'm using Postgresql 15.1

This is a link to a full working testable schema and query

Improve this question
3
  • Can you share the CREATE TABLE and CREATE INDEX statements?
    – jjanes
    Feb 25 at 0:12
  • Ok. I'll update question with CREATE TABLE/INDEX. Meantime an update: If I add order by "ccc" desc,"id" desc to the view then the performance improve like the 'WITH' version. Not sure why this happen.
    – Fabiano Taioli
    Feb 26 at 12:56
  • @jjanes added a link to a working CREATE TABLE/INDEX and test query.
    – Fabiano Taioli
    Mar 3 at 9:04

1 Answer 1

Reset to default
0

According to this answer (Improving performance of nasty nested-view joins) probably the presence of a LEFT JOIN prevents the planner to push the ORDER BY (and the LIMIT) into the view.

The LIMIT 25 is what makes the second query faster, because it stops the inner loops after the first 25 rows. Without the limit both queries should take the same time.

Adding ORDER BY to the view allows the planner to push the LIMIT into the view (since the order of the view is now the same of the outer query) resulting in the performance improvement.

Improve this answer
5
  • Is not clear to me when you say "The LIMIT 25 is what makes the query faster.. Without the limit both queries should take the same time.". Limit 25 is used in both queries, and one is much much slower.
    – Fabiano Taioli
    Mar 3 at 10:30
  • About the linked answer, is a 10 years old answer. Will be very strange to me if in 10 years the Postgres planner has not improved in that issue.
    – Fabiano Taioli
    Mar 3 at 10:34
  • @FabianoTaioli Removing LIMIT 25 from both queries they should both take 9 seconds. Adding LIMIT 25 the second one gets down to just 1 ms. In the second one, the inner loop produces row ordered by "ccc" and "id" desc, and stops after the first 25 rows. In the first one the view will instead produce 155463 unsorted rows, which will then be sorted by the outer query and all but the first 25 will be discarded. So the difference between the two is the fact that the optimizer is pushing down ORDER BY into the CTE but doesn't it with the VIEW.
    – Andrea B.
    Mar 6 at 12:07
  • "the optimizer is pushing down ORDER BY into the CTE but doesn't it with the VIEW" But do you think this behavior is a PostgreSQL planner bug/limitation?
    – Fabiano Taioli
    Mar 6 at 15:52
  • @FabianoTaioli Pushing external "predicates" (such as WHERE clauses, ORDER BY, LIMIT) into inner subqueries, views or CTEs is possible only if the planner can compute that the resulting query will be faster but semantically identical to the original. This computation can be done only when the query involved are simple. It becomes more difficult when LEFT JOINS or GROUP BY are involved. Also, your example suggests that it's handled differently with VIEWs rather than with CTEs or maybe inlined subqueries. I don't think it's a bug, but rather a limitation imposed by the computation complexity.
    – Andrea B.
    Mar 6 at 16:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.