explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oxRY

Settings
# exclusive inclusive rows x rows loops node
1. 71.042 9,613.663 ↑ 285,753.9 2,423 1

Merge Left Join (cost=1,375,470.81..37,728,533.44 rows=692,381,755 width=3,222) (actual time=9,465.717..9,613.663 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. 96.216 178.144 ↑ 14.7 87,253 1

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

4.          

Initplan (for Nested Loop)

5. 0.270 0.270 ↑ 1.0 1 1

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

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

Seq Scan on myvars myvars_1 (cost=0.00..19.85 rows=1 width=4) (actual time=0.130..0.131 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.150 0.150 ↑ 1.0 1 1

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

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

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

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

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

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

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

  • Hash Cond: (user_gest_1.gest = mygestiuni_1.idgest)
12. 0.025 0.343 ↓ 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.338..0.343 rows=92 loops=1)

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

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

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

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

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

Seq Scan on mygestiuni mygestiuni_1 (cost=0.00..4.09 rows=46 width=4) (actual time=0.420..0.431 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. 80.425 80.425 ↑ 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.036..16.085 rows=17,451 loops=5)

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

CTE t2

18. 154.325 8,474.991 ↑ 6.8 86,148 1

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

  • Group Key: docout.lotout
19. 1,387.783 8,320.666 ↑ 11.4 593,635 1

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

  • Merge Cond: (docout.lotout = t1_1.lot)
20. 6,847.416 6,847.416 ↓ 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.039..6,847.416 rows=13,583,054 loops=1)

21. 42.109 85.467 ↓ 436.2 87,246 1

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

  • Sort Key: t1_1.lot
  • Sort Method: quicksort Memory: 7,162kB
22. 31.860 43.358 ↓ 436.3 87,253 1

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

  • Group Key: t1_1.lot
23. 11.498 11.498 ↑ 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.001..11.498 rows=87,253 loops=1)

24.          

Initplan (for Merge Left Join)

25. 0.121 0.121 ↑ 1.0 1 1

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

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

Sort (cost=316,196.30..316,789.46 rows=237,264 width=3,194) (actual time=930.858..993.982 rows=87,253 loops=1)

  • Sort Key: t1.lot
  • Sort Method: external merge Disk: 75,608kB
27. 127.624 632.175 ↑ 2.7 87,253 1

Nested Loop (cost=31.30..103,590.36 rows=237,264 width=3,194) (actual time=1.273..632.175 rows=87,253 loops=1)

28. 32.405 330.045 ↑ 2.7 87,253 1

Hash Join (cost=31.01..29,778.31 rows=237,264 width=1,609) (actual time=1.198..330.045 rows=87,253 loops=1)

  • Hash Cond: (user_gest.gest = mygestiuni.idgest)
29. 45.063 297.580 ↑ 2.7 87,253 1

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

  • Hash Cond: (t1.gestid = user_gest.gest)
30. 252.358 252.358 ↑ 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.942..252.358 rows=87,253 loops=1)

31. 0.014 0.159 ↓ 2.5 92 1

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

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

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

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

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

  • Index Cond: ((username)::text = ($8)::text)
34. 0.030 0.060 ↑ 1.0 93 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
35. 0.030 0.030 ↑ 1.0 93 1

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

36. 174.506 174.506 ↑ 1.0 1 87,253

Index Scan using idxart_idart on articole t3 (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)
37. 9.332 8,548.518 ↑ 6.8 86,148 1

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

38. 29.265 8,539.186 ↑ 6.8 86,148 1

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

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

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

Planning time : 15.299 ms
Execution time : 9,628.549 ms