explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nwg6

Settings
# exclusive inclusive rows x rows loops node
1. 30.860 1,616.084 ↑ 15,961.9 1,837 1

Merge Left Join (cost=547,957.56..2,088,887.13 rows=29,321,987 width=3,222) (actual time=1,556.109..1,616.084 rows=1,837 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: 27,570
2.          

CTE t1

3. 67.745 71.822 ↑ 3.7 29,407 1

Bitmap Heap Scan on docin d (cost=1,191.05..98,546.45 rows=108,628 width=336) (actual time=4.680..71.822 rows=29,407 loops=1)

  • Recheck Cond: (gestid = $0)
  • Filter: valid
  • Heap Blocks: exact=5,631
4.          

Initplan (for Bitmap Heap Scan)

5. 0.281 0.281 ↑ 1.0 1 1

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

  • Filter: (lower((username)::text) = lower((("current_user"())::character varying)::text))
  • Rows Removed by Filter: 348
6. 3.796 3.796 ↑ 3.7 29,407 1

Bitmap Index Scan on idxdocingestid (cost=0.00..1,144.04 rows=108,628 width=0) (actual time=3.796..3.796 rows=29,407 loops=1)

  • Index Cond: (gestid = $0)
7.          

CTE t2

8. 132.425 1,165.999 ↑ 20.2 28,882 1

GroupAggregate (cost=302,158.11..353,637.94 rows=583,648 width=48) (actual time=969.802..1,165.999 rows=28,882 loops=1)

  • Group Key: docout.lotout
9. 245.408 1,033.574 ↑ 4.7 536,045 1

Sort (cost=302,158.11..308,470.14 rows=2,524,813 width=22) (actual time=969.776..1,033.574 rows=536,045 loops=1)

  • Sort Key: docout.lotout
  • Sort Method: external merge Disk: 16,168kB
10. 150.462 788.166 ↑ 4.7 536,045 1

Nested Loop (cost=2,444.57..3,345.01 rows=2,524,813 width=22) (actual time=10.368..788.166 rows=536,045 loops=1)

11. 17.300 20.157 ↓ 147.0 29,407 1

HashAggregate (cost=2,444.13..2,446.13 rows=200 width=4) (actual time=10.295..20.157 rows=29,407 loops=1)

  • Group Key: t1_2.lot
12. 2.857 2.857 ↑ 3.7 29,407 1

CTE Scan on t1 t1_2 (cost=0.00..2,172.56 rows=108,628 width=4) (actual time=0.001..2.857 rows=29,407 loops=1)

13. 617.547 617.547 ↑ 1.3 18 29,407

Index Scan using idxdocoutvalidcantout on docout (cost=0.43..4.26 rows=23 width=22) (actual time=0.005..0.021 rows=18 loops=29,407)

  • Index Cond: (lotout = t1_2.lot)
14.          

Initplan (for Merge Left Join)

15. 0.168 0.168 ↑ 1.0 1 1

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

  • Filter: (lower((username)::text) = lower((("current_user"())::character varying)::text))
  • Rows Removed by Filter: 348
16. 134.344 396.314 ↓ 2.9 29,407 1

Sort (cost=17,665.73..17,690.85 rows=10,048 width=3,194) (actual time=372.669..396.314 rows=29,407 loops=1)

  • Sort Key: t1.lot
  • Sort Method: external merge Disk: 25,400kB
17. 50.311 261.970 ↓ 2.9 29,407 1

Nested Loop (cost=2,479.93..8,890.06 rows=10,048 width=3,194) (actual time=105.761..261.970 rows=29,407 loops=1)

  • Join Filter: (t1_1.artid = a.idart)
18. 11.257 152.845 ↓ 2.9 29,407 1

Hash Join (cost=2,479.64..5,247.96 rows=10,048 width=1,613) (actual time=105.704..152.845 rows=29,407 loops=1)

  • Hash Cond: (user_gest.gest = mygestiuni.idgest)
19. 12.560 141.513 ↓ 2.9 29,407 1

Hash Join (cost=2,475.55..5,216.29 rows=10,048 width=1,403) (actual time=105.607..141.513 rows=29,407 loops=1)

  • Hash Cond: (t1.artid = t1_1.artid)
20. 14.143 28.319 ↓ 1.5 29,407 1

Hash Join (cost=26.92..2,491.34 rows=20,096 width=1,399) (actual time=4.955..28.319 rows=29,407 loops=1)

  • Hash Cond: (t1.gestid = user_gest.gest)
21. 13.920 13.920 ↑ 3.7 29,407 1

CTE Scan on t1 (cost=0.00..2,172.56 rows=108,628 width=1,395) (actual time=4.690..13.920 rows=29,407 loops=1)

22. 0.015 0.256 ↓ 2.5 92 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
23. 0.029 0.241 ↓ 2.5 92 1

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

  • Recheck Cond: ((username)::text = ($4)::text)
  • Heap Blocks: exact=1
24. 0.212 0.212 ↓ 2.5 92 1

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

  • Index Cond: ((username)::text = ($4)::text)
25. 0.376 100.634 ↓ 14.9 2,978 1

Hash (cost=2,446.13..2,446.13 rows=200 width=4) (actual time=100.634..100.634 rows=2,978 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 137kB
26. 6.662 100.258 ↓ 14.9 2,978 1

HashAggregate (cost=2,444.13..2,446.13 rows=200 width=4) (actual time=99.949..100.258 rows=2,978 loops=1)

  • Group Key: t1_1.artid
27. 93.596 93.596 ↑ 3.7 29,407 1

CTE Scan on t1 t1_1 (cost=0.00..2,172.56 rows=108,628 width=4) (actual time=0.001..93.596 rows=29,407 loops=1)

28. 0.043 0.075 ↑ 1.0 93 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
29. 0.032 0.032 ↑ 1.0 93 1

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

30. 58.814 58.814 ↑ 1.0 1 29,407

Index Scan using idxart_idart on articole a (cost=0.29..0.35 rows=1 width=1,585) (actual time=0.002..0.002 rows=1 loops=29,407)

  • Index Cond: (idart = t1.artid)
31. 3.760 1,188.742 ↑ 20.2 28,882 1

Materialize (cost=78,087.59..81,005.83 rows=583,648 width=48) (actual time=1,183.344..1,188.742 rows=28,882 loops=1)

32. 8.353 1,184.982 ↑ 20.2 28,882 1

Sort (cost=78,087.59..79,546.71 rows=583,648 width=48) (actual time=1,183.341..1,184.982 rows=28,882 loops=1)

  • Sort Key: t2.lotout
  • Sort Method: quicksort Memory: 3,025kB
33. 1,176.629 1,176.629 ↑ 20.2 28,882 1

CTE Scan on t2 (cost=0.00..11,672.96 rows=583,648 width=48) (actual time=969.807..1,176.629 rows=28,882 loops=1)

Planning time : 15.555 ms
Execution time : 1,623.010 ms