explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ioWM

Settings
# exclusive inclusive rows x rows loops node
1. 70.060 11,396.909 ↑ 285,753.9 2,423 1

Merge Left Join (cost=1,452,625.40..37,805,688.03 rows=692,381,755 width=3,222) (actual time=11,241.027..11,396.909 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.123 182.568 ↑ 14.7 87,253 1

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

4.          

Initplan (for Nested Loop)

5. 0.278 0.278 ↑ 1.0 1 1

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

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

Seq Scan on myvars myvars_1 (cost=0.00..19.85 rows=1 width=4) (actual time=0.170..0.170 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.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.016 0.901 ↑ 3.6 5 1

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

  • Group Key: mygestiuni_1.idgest
11. 0.030 0.885 ↑ 3.6 5 1

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

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

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

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

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

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

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

Seq Scan on mygestiuni mygestiuni_1 (cost=0.00..4.09 rows=46 width=4) (actual time=0.480..0.493 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. 84.800 84.800 ↑ 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.028..16.960 rows=17,451 loops=5)

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

CTE t2

18. 241.955 10,194.945 ↑ 6.8 86,164 1

GroupAggregate (cost=28,866.51..865,611.21 rows=583,648 width=48) (actual time=70.135..10,194.945 rows=86,164 loops=1)

  • Group Key: docout.lotout
19. 862.016 9,952.990 ↑ 6.7 1,123,790 1

Merge Join (cost=28,866.51..745,811.59 rows=7,500,268 width=22) (actual time=68.918..9,952.990 rows=1,123,790 loops=1)

  • Merge Cond: (docout.lotout = t1_1.lot)
20. 9,000.275 9,000.275 ↑ 1.0 14,997,960 1

Index Scan using idxdocoutlotout on docout (cost=0.43..679,391.78 rows=15,000,535 width=22) (actual time=0.044..9,000.275 rows=14,997,960 loops=1)

  • Filter: valid
21. 44.270 90.699 ↓ 436.2 87,246 1

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

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

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

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

24.          

Initplan (for Merge Left Join)

25. 0.158 0.158 ↑ 1.0 1 1

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

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

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

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

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

28. 36.477 345.425 ↑ 2.7 87,253 1

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

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

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

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

31. 0.017 0.204 ↓ 2.5 92 1

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

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

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

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

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

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

Hash (cost=2.93..2.93 rows=93 width=214) (actual time=0.065..0.065 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.011..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.273 10,270.480 ↑ 6.8 86,164 1

Materialize (cost=78,087.59..81,005.83 rows=583,648 width=48) (actual time=10,256.824..10,270.480 rows=86,164 loops=1)

38. 28.603 10,261.207 ↑ 6.8 86,164 1

Sort (cost=78,087.59..79,546.71 rows=583,648 width=48) (actual time=10,256.820..10,261.207 rows=86,164 loops=1)

  • Sort Key: t2.lotout
  • Sort Method: quicksort Memory: 9,804kB
39. 10,232.604 10,232.604 ↑ 6.8 86,164 1

CTE Scan on t2 (cost=0.00..11,672.96 rows=583,648 width=48) (actual time=70.139..10,232.604 rows=86,164 loops=1)

Planning time : 16.082 ms
Execution time : 11,412.774 ms