explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BGd

Settings
# exclusive inclusive rows x rows loops node
1. 100.175 3,154.910 ↓ 17,404.9 330,693 1

Hash Left Join (cost=3,909.11..3,910.10 rows=19 width=136) (actual time=2,634.439..3,154.910 rows=330,693 loops=1)

  • Hash Cond: (((gb.industrysector)::text = (secb.industrysector)::text) AND (gb.optioncontractquoteintervalbatchinstant = secb.optioncontractquoteintervalbatchinstant))
  • Planning Time: 4.833 ms
  • Execution Time: 3170.771 ms
2.          

CTE optioncontractbatch

3. 336.923 2,133.297 ↓ 17,404.9 330,693 1

Nested Loop (cost=183.62..3,903.12 rows=19 width=78) (actual time=4.128..2,133.297 rows=330,693 loops=1)

4. 112.776 1,465.681 ↓ 17,404.9 330,693 1

Nested Loop (cost=183.20..3,891.84 rows=19 width=106) (actual time=4.115..1,465.681 rows=330,693 loops=1)

5. 105.943 691.519 ↓ 17,404.9 330,693 1

Nested Loop (cost=182.77..3,880.89 rows=19 width=94) (actual time=4.072..691.519 rows=330,693 loops=1)

6. 65.796 80.280 ↓ 121.3 84,216 1

Hash Join (cost=182.35..3,239.04 rows=694 width=78) (actual time=4.053..80.280 rows=84,216 loops=1)

  • Hash Cond: (((ocs.instant)::date = (ss.instant)::date) AND (ocs.stockid = ss.stockid))
7. 10.465 10.465 ↑ 1.0 84,216 1

Seq Scan on optioncontractsnapshot ocs (cost=0.00..1,786.30 rows=84,230 width=25) (actual time=0.014..10.465 rows=84,216 loops=1)

8. 2.233 4.019 ↓ 1.0 4,255 1

Hash (cost=118.54..118.54 rows=4,254 width=61) (actual time=4.019..4.019 rows=4,255 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 476kB
9. 1.786 1.786 ↓ 1.0 4,255 1

Seq Scan on stocksnapshot ss (cost=0.00..118.54 rows=4,254 width=61) (actual time=0.010..1.786 rows=4,255 loops=1)

10. 505.296 505.296 ↓ 4.0 4 84,216

Index Scan using optioncontractquoteinterval_pkey on optioncontractquoteinterval ocqi (cost=0.42..0.91 rows=1 width=32) (actual time=0.003..0.006 rows=4 loops=84,216)

  • Index Cond: (optioncontractid = ocs.optioncontractid)
  • Filter: ((ocs.instant)::date = (leftoptioncontractquoteinstant)::date)
  • Rows Removed by Filter: 3
11. 661.386 661.386 ↑ 1.0 1 330,693

Index Scan using optioncontractquote_pkey on optioncontractquote locq (cost=0.42..0.58 rows=1 width=28) (actual time=0.002..0.002 rows=1 loops=330,693)

  • Index Cond: ((optioncontractid = ocqi.optioncontractid) AND (instant = ocqi.leftoptioncontractquoteinstant))
12. 330.693 330.693 ↑ 1.0 1 330,693

Index Scan using optioncontractquote_pkey on optioncontractquote rocq (cost=0.42..0.58 rows=1 width=28) (actual time=0.001..0.001 rows=1 loops=330,693)

  • Index Cond: ((optioncontractid = ocqi.optioncontractid) AND (instant = ocqi.rightoptioncontractquoteinstant))
13.          

CTE stockbatch

14. 281.001 2,560.550 ↓ 2,125.3 40,381 1

HashAggregate (cost=0.71..0.90 rows=19 width=128) (actual time=2,543.482..2,560.550 rows=40,381 loops=1)

  • Group Key: ocb_1.stockid, ocb_1.industrysubgroup, ocb_1.industrygroup, ocb_1.industrysector, ocb_1.optioncontractquoteintervalbatchinstant
15. 2,279.549 2,279.549 ↓ 17,404.9 330,693 1

CTE Scan on optioncontractbatch ocb_1 (cost=0.00..0.38 rows=19 width=121) (actual time=0.001..2,279.549 rows=330,693 loops=1)

16.          

CTE subgroupbatch

17. 20.979 25.508 ↓ 312.3 5,933 1

HashAggregate (cost=0.67..0.85 rows=19 width=120) (actual time=24.131..25.508 rows=5,933 loops=1)

  • Group Key: sb_1.industrysubgroup, sb_1.industrygroup, sb_1.industrysector, sb_1.optioncontractquoteintervalbatchinstant
18. 4.529 4.529 ↓ 2,125.3 40,381 1

CTE Scan on stockbatch sb_1 (cost=0.00..0.38 rows=19 width=120) (actual time=0.001..4.529 rows=40,381 loops=1)

19.          

CTE groupbatch

20. 3.070 3.586 ↓ 160.0 3,040 1

HashAggregate (cost=0.62..0.81 rows=19 width=88) (actual time=3.035..3.586 rows=3,040 loops=1)

  • Group Key: sgb_1.industrygroup, sgb_1.industrysector, sgb_1.optioncontractquoteintervalbatchinstant
21. 0.516 0.516 ↓ 312.3 5,933 1

CTE Scan on subgroupbatch sgb_1 (cost=0.00..0.38 rows=19 width=88) (actual time=0.000..0.516 rows=5,933 loops=1)

22.          

CTE sectorbatch

23. 1.041 1.289 ↓ 28.4 540 1

HashAggregate (cost=0.57..0.76 rows=19 width=56) (actual time=1.209..1.289 rows=540 loops=1)

  • Group Key: gb_1.industrysector, gb_1.optioncontractquoteintervalbatchinstant
24. 0.248 0.248 ↓ 160.0 3,040 1

CTE Scan on groupbatch gb_1 (cost=0.00..0.38 rows=19 width=56) (actual time=0.000..0.248 rows=3,040 loops=1)

25. 109.371 3,053.193 ↓ 17,404.9 330,693 1

Hash Left Join (cost=1.99..2.83 rows=19 width=176) (actual time=2,632.887..3,053.193 rows=330,693 loops=1)

  • Hash Cond: (((sgb.industrygroup)::text = (gb.industrygroup)::text) AND (sgb.optioncontractquoteintervalbatchinstant = gb.optioncontractquoteintervalbatchinstant))
26. 121.313 2,938.308 ↓ 17,404.9 330,693 1

Hash Left Join (cost=1.33..2.01 rows=19 width=128) (actual time=2,627.360..2,938.308 rows=330,693 loops=1)

  • Hash Cond: (((sb.industrysubgroup)::text = (sgb.industrysubgroup)::text) AND (sb.optioncontractquoteintervalbatchinstant = sgb.optioncontractquoteintervalbatchinstant))
27. 143.983 2,787.475 ↓ 17,404.9 330,693 1

Hash Left Join (cost=0.67..1.20 rows=19 width=80) (actual time=2,597.824..2,787.475 rows=330,693 loops=1)

  • Hash Cond: ((ocb.stockid = sb.stockid) AND (ocb.optioncontractquoteintervalbatchinstant = sb.optioncontractquoteintervalbatchinstant))
28. 49.811 49.811 ↓ 17,404.9 330,693 1

CTE Scan on optioncontractbatch ocb (cost=0.00..0.38 rows=19 width=32) (actual time=4.131..49.811 rows=330,693 loops=1)

29. 10.485 2,593.681 ↓ 2,125.3 40,381 1

Hash (cost=0.38..0.38 rows=19 width=64) (actual time=2,593.681..2,593.681 rows=40,381 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 1 (originally 1) Memory Usage: 3747kB
30. 2,583.196 2,583.196 ↓ 2,125.3 40,381 1

CTE Scan on stockbatch sb (cost=0.00..0.38 rows=19 width=64) (actual time=2,543.484..2,583.196 rows=40,381 loops=1)

31. 1.417 29.520 ↓ 312.3 5,933 1

Hash (cost=0.38..0.38 rows=19 width=88) (actual time=29.520..29.520 rows=5,933 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 609kB
32. 28.103 28.103 ↓ 312.3 5,933 1

CTE Scan on subgroupbatch sgb (cost=0.00..0.38 rows=19 width=88) (actual time=24.135..28.103 rows=5,933 loops=1)

33. 0.778 5.514 ↓ 160.0 3,040 1

Hash (cost=0.38..0.38 rows=19 width=88) (actual time=5.514..5.514 rows=3,040 loops=1)

  • Buckets: 4096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 300kB
34. 4.736 4.736 ↓ 160.0 3,040 1

CTE Scan on groupbatch gb (cost=0.00..0.38 rows=19 width=88) (actual time=3.036..4.736 rows=3,040 loops=1)

35. 0.095 1.542 ↓ 28.4 540 1

Hash (cost=0.38..0.38 rows=19 width=40) (actual time=1.542..1.542 rows=540 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 39kB
36. 1.447 1.447 ↓ 28.4 540 1

CTE Scan on sectorbatch secb (cost=0.00..0.38 rows=19 width=40) (actual time=1.211..1.447 rows=540 loops=1)