explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T9Y7

Settings
# exclusive inclusive rows x rows loops node
1. 71.082 9,951.595 ↑ 142,877.0 2,423 1

Merge Left Join (cost=1,264,950.29..19,442,211.19 rows=346,190,878 width=3,222) (actual time=9,810.535..9,951.595 rows=2,423 loops=1)

  • Merge Cond: (t1.lot = t2.lotout)
  • Filter: ((((t1.cantrec)::double precision - COALESCE((t2.qout)::double precision, '0'::double precision)) > '1e-05'::double precision) OR (COALESCE((t2.qpro)::double precision, '0'::double precision) <> '0'::double precision) OR (COALESCE(t2.qtvliv, '0'::double precision) <> '0'::double precision))
  • Rows Removed by Filter: 84,830
2.          

CTE t1

3. 86.787 163.170 ↑ 14.7 87,253 1

Nested Loop (cost=130.96..192,710.45 rows=1,282,508 width=336) (actual time=0.882..163.170 rows=87,253 loops=1)

4.          

Initplan (for Nested Loop)

5. 0.247 0.247 ↑ 1.0 1 1

Seq Scan on myvars (cost=0.00..19.85 rows=1 width=10) (actual time=0.246..0.247 rows=1 loops=1)

  • Filter: (lower((username)::text) = lower((("current_user"())::character varying)::text))
  • Rows Removed by Filter: 348
6. 0.152 0.152 ↑ 1.0 1 1

Seq Scan on myvars myvars_1 (cost=0.00..19.85 rows=1 width=4) (actual time=0.152..0.152 rows=1 loops=1)

  • Filter: (lower((username)::text) = lower((("current_user"())::character varying)::text))
  • Rows Removed by Filter: 348
7. 0.000 0.000 ↓ 0.0 0

Seq Scan on myvars myvars_2 (cost=0.00..19.85 rows=1 width=4) (never executed)

  • Filter: (lower((username)::text) = lower((("current_user"())::character varying)::text))
8. 0.147 0.147 ↑ 1.0 1 1

Seq Scan on myvars myvars_3 (cost=0.00..19.85 rows=1 width=1) (actual time=0.146..0.147 rows=1 loops=1)

  • Filter: (lower((username)::text) = lower((("current_user"())::character varying)::text))
  • Rows Removed by Filter: 348
9. 0.146 0.146 ↑ 1.0 1 1

Seq Scan on myvars myvars_4 (cost=0.00..19.85 rows=1 width=1) (actual time=0.145..0.146 rows=1 loops=1)

  • Filter: (lower((username)::text) = lower((("current_user"())::character varying)::text))
  • Rows Removed by Filter: 348
10. 0.012 0.821 ↑ 3.6 5 1

HashAggregate (cost=31.26..31.44 rows=18 width=8) (actual time=0.813..0.821 rows=5 loops=1)

  • Group Key: mygestiuni_1.idgest
11. 0.031 0.809 ↑ 3.6 5 1

Hash Join (cost=6.34..31.22 rows=18 width=8) (actual time=0.804..0.809 rows=5 loops=1)

  • Hash Cond: (user_gest_1.gest = mygestiuni_1.idgest)
12. 0.021 0.309 ↓ 2.5 92 1

Bitmap Heap Scan on user_gest user_gest_1 (cost=1.67..26.45 rows=37 width=4) (actual time=0.303..0.309 rows=92 loops=1)

  • Recheck Cond: ((username)::text = ($0)::text)
  • Heap Blocks: exact=1
13. 0.288 0.288 ↓ 2.5 92 1

Bitmap Index Scan on idx_usergest_username (cost=0.00..1.66 rows=37 width=0) (actual time=0.288..0.288 rows=92 loops=1)

  • Index Cond: ((username)::text = ($0)::text)
14. 0.006 0.469 ↑ 9.2 5 1

Hash (cost=4.09..4.09 rows=46 width=4) (actual time=0.468..0.469 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
15. 0.463 0.463 ↑ 9.2 5 1

Seq Scan on mygestiuni mygestiuni_1 (cost=0.00..4.09 rows=46 width=4) (actual time=0.451..0.463 rows=5 loops=1)

  • Filter: CASE ($1 <> 0) WHEN CASE_TEST_EXPR THEN (idgest = $2) ELSE CASE (($3)::text <> ''::text) WHEN CASE_TEST_EXPR THEN ((grupagestiune)::text = ($4)::text) ELSE (idgest <> 0) END END
  • Rows Removed by Filter: 88
16. 74.870 74.870 ↑ 6.2 17,451 5

Index Scan using idxdocingestid on docin d (cost=0.43..8,721.96 rows=108,628 width=328) (actual time=0.029..14.974 rows=17,451 loops=5)

  • Index Cond: (gestid = mygestiuni_1.idgest)
  • Filter: valid
  • Rows Removed by Filter: 0
17.          

CTE t2

18. 155.720 8,762.776 ↑ 6.8 86,148 1

GroupAggregate (cost=28,866.51..788,456.62 rows=583,648 width=48) (actual time=71.247..8,762.776 rows=86,148 loops=1)

  • Group Key: docout.lotout
19. 1,412.554 8,607.056 ↑ 11.4 593,635 1

Merge Join (cost=28,866.51..679,419.14 rows=6,782,792 width=22) (actual time=70.100..8,607.056 rows=593,635 loops=1)

  • Merge Cond: (docout.lotout = t1_2.lot)
20. 7,103.081 7,103.081 ↓ 1.0 13,583,054 1

Index Scan using idxdocoutvalidcantout on docout (cost=0.43..616,591.62 rows=13,565,584 width=22) (actual time=0.032..7,103.081 rows=13,583,054 loops=1)

21. 44.641 91.421 ↓ 436.2 87,246 1

Sort (cost=28,866.07..28,866.57 rows=200 width=4) (actual time=69.068..91.421 rows=87,246 loops=1)

  • Sort Key: t1_2.lot
  • Sort Method: quicksort Memory: 7,162kB
22. 35.342 46.780 ↓ 436.3 87,253 1

HashAggregate (cost=28,856.43..28,858.43 rows=200 width=4) (actual time=34.221..46.780 rows=87,253 loops=1)

  • Group Key: t1_2.lot
23. 11.438 11.438 ↑ 14.7 87,253 1

CTE Scan on t1 t1_2 (cost=0.00..25,650.16 rows=1,282,508 width=4) (actual time=0.002..11.438 rows=87,253 loops=1)

24.          

Initplan (for Merge Left Join)

25. 0.160 0.160 ↑ 1.0 1 1

Seq Scan on myvars myvars_5 (cost=0.00..19.85 rows=1 width=10) (actual time=0.160..0.160 rows=1 loops=1)

  • Filter: (lower((username)::text) = lower((("current_user"())::character varying)::text))
  • Rows Removed by Filter: 348
26. 371.297 1,042.595 ↑ 1.4 87,253 1

Sort (cost=205,675.77..205,972.35 rows=118,632 width=3,194) (actual time=986.839..1,042.595 rows=87,253 loops=1)

  • Sort Key: t1.lot
  • Sort Method: external merge Disk: 75,608kB
27. 111.678 671.298 ↑ 1.4 87,253 1

Nested Loop (cost=28,892.23..99,964.94 rows=118,632 width=3,194) (actual time=259.857..671.298 rows=87,253 loops=1)

  • Join Filter: (t1_1.artid = a.idart)
28. 31.190 385.114 ↑ 1.4 87,253 1

Hash Join (cost=28,891.94..61,576.03 rows=118,632 width=1,613) (actual time=259.809..385.114 rows=87,253 loops=1)

  • Hash Cond: (user_gest.gest = mygestiuni.idgest)
29. 29.772 353.863 ↑ 1.4 87,253 1

Hash Join (cost=28,887.85..61,246.33 rows=118,632 width=1,403) (actual time=259.728..353.863 rows=87,253 loops=1)

  • Hash Cond: (t1.artid = t1_1.artid)
30. 39.870 65.487 ↑ 2.7 87,253 1

Hash Join (cost=26.92..29,123.02 rows=237,264 width=1,399) (actual time=1.104..65.487 rows=87,253 loops=1)

  • Hash Cond: (t1.gestid = user_gest.gest)
31. 25.418 25.418 ↑ 14.7 87,253 1

CTE Scan on t1 (cost=0.00..25,650.16 rows=1,282,508 width=1,395) (actual time=0.889..25.418 rows=87,253 loops=1)

32. 0.015 0.199 ↓ 2.5 92 1

Hash (cost=26.45..26.45 rows=37 width=4) (actual time=0.199..0.199 rows=92 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
33. 0.014 0.184 ↓ 2.5 92 1

Bitmap Heap Scan on user_gest (cost=1.67..26.45 rows=37 width=4) (actual time=0.174..0.184 rows=92 loops=1)

  • Recheck Cond: ((username)::text = ($8)::text)
  • Heap Blocks: exact=1
34. 0.170 0.170 ↓ 2.5 92 1

Bitmap Index Scan on idx_usergest_username (cost=0.00..1.66 rows=37 width=0) (actual time=0.170..0.170 rows=92 loops=1)

  • Index Cond: ((username)::text = ($8)::text)
35. 0.489 258.604 ↓ 16.2 3,249 1

Hash (cost=28,858.43..28,858.43 rows=200 width=4) (actual time=258.603..258.604 rows=3,249 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 147kB
36. 17.292 258.115 ↓ 16.2 3,249 1

HashAggregate (cost=28,856.43..28,858.43 rows=200 width=4) (actual time=257.708..258.115 rows=3,249 loops=1)

  • Group Key: t1_1.artid
37. 240.823 240.823 ↑ 14.7 87,253 1

CTE Scan on t1 t1_1 (cost=0.00..25,650.16 rows=1,282,508 width=4) (actual time=0.003..240.823 rows=87,253 loops=1)

38. 0.034 0.061 ↑ 1.0 93 1

Hash (cost=2.93..2.93 rows=93 width=214) (actual time=0.061..0.061 rows=93 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
39. 0.027 0.027 ↑ 1.0 93 1

Seq Scan on mygestiuni (cost=0.00..2.93 rows=93 width=214) (actual time=0.009..0.027 rows=93 loops=1)

40. 174.506 174.506 ↑ 1.0 1 87,253

Index Scan using idxart_idart on articole a (cost=0.29..0.31 rows=1 width=1,585) (actual time=0.002..0.002 rows=1 loops=87,253)

  • Index Cond: (idart = t1.artid)
41. 9.548 8,837.758 ↑ 6.8 86,148 1

Materialize (cost=78,087.59..81,005.83 rows=583,648 width=48) (actual time=8,823.620..8,837.758 rows=86,148 loops=1)

42. 29.932 8,828.210 ↑ 6.8 86,148 1

Sort (cost=78,087.59..79,546.71 rows=583,648 width=48) (actual time=8,823.616..8,828.210 rows=86,148 loops=1)

  • Sort Key: t2.lotout
  • Sort Method: quicksort Memory: 9,803kB
43. 8,798.278 8,798.278 ↑ 6.8 86,148 1

CTE Scan on t2 (cost=0.00..11,672.96 rows=583,648 width=48) (actual time=71.251..8,798.278 rows=86,148 loops=1)

Planning time : 14.287 ms
Execution time : 9,964.696 ms