explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 751A

Settings
# exclusive inclusive rows x rows loops node
1. 3,551.173 92,113.333 ↓ 1.1 2,409 1

GroupAggregate (cost=1,235,262,517.74..1,283,839,913.26 rows=2,103 width=70) (actual time=85,527.023..92,113.333 rows=2,409 loops=1)

  • Group Key: (date_trunc('day'::text, oc.snapshot_time)), oc.symbol
  • Functions: 58
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 34.842 ms, Inlining 26.284 ms, Optimization 569.151 ms, Emission 323.019 ms, Total 953.296 ms
2.          

CTE oc

3. 107.620 107.620 ↑ 1.0 20,956 1

Index Scan using option_chains_snapshot_time_ix on option_chains oc2 (cost=0.30..815.48 rows=21,031 width=20) (actual time=35.425..107.620 rows=20,956 loops=1)

  • Index Cond: ((snapshot_time >= (now() - '5 days'::interval)) AND (snapshot_time <= now()))
4.          

CTE ec

5. 146.848 464.751 ↓ 1.1 198,191 1

Hash Join (cost=477.70..11,284.84 rows=186,863 width=43) (actual time=59.417..464.751 rows=198,191 loops=1)

  • Hash Cond: (ec2.option_chain_id = oc_1.option_chain_id)
6. 290.561 290.561 ↑ 1.0 373,726 1

Seq Scan on expiration_cycles ec2 (cost=0.00..7,747.26 rows=373,726 width=43) (actual time=4.947..290.561 rows=373,726 loops=1)

7. 7.349 27.342 ↓ 104.8 20,956 1

Hash (cost=475.20..475.20 rows=200 width=8) (actual time=27.341..27.342 rows=20,956 loops=1)

  • Buckets: 32,768 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,075kB
8. 15.001 19.993 ↓ 104.8 20,956 1

HashAggregate (cost=473.20..475.20 rows=200 width=8) (actual time=13.988..19.993 rows=20,956 loops=1)

  • Group Key: oc_1.option_chain_id
  • Batches: 1 Memory Usage: 1,825kB
9. 4.992 4.992 ↑ 1.0 20,956 1

CTE Scan on oc oc_1 (cost=0.00..420.62 rows=21,031 width=8) (actual time=0.016..4.992 rows=20,956 loops=1)

10. 27,515.082 88,562.160 ↑ 450.2 7,194,012 1

Sort (cost=1,235,250,417.42..1,243,346,645.63 rows=3,238,491,282 width=70) (actual time=85,524.870..88,562.160 rows=7,194,012 loops=1)

  • Sort Key: (date_trunc('day'::text, oc.snapshot_time)) DESC, oc.symbol
  • Sort Method: external merge Disk: 352,032kB
11. 12,160.797 61,047.078 ↑ 450.2 7,194,012 1

Merge Join (cost=2,116,785.46..59,543,684.23 rows=3,238,491,282 width=70) (actual time=19,109.230..61,047.078 rows=7,194,012 loops=1)

  • Merge Cond: (ecr2.expiration_id = ec_1.expiration_id)
12. 45,168.205 45,168.205 ↑ 1.0 13,639,532 1

Index Scan using expiration_cycle_rows_expid_btree_ix on expiration_cycle_rows ecr2 (cost=0.43..694,638.50 rows=13,639,532 width=16) (actual time=48.104..45,168.205 rows=13,639,532 loops=1)

13. 1,343.255 3,718.076 ↑ 1.4 7,193,985 1

Materialize (cost=2,116,785.02..2,165,908.97 rows=9,824,790 width=70) (actual time=1,689.486..3,718.076 rows=7,193,985 loops=1)

14. 794.693 2,374.821 ↑ 49.6 198,191 1

Sort (cost=2,116,785.02..2,141,347.00 rows=9,824,790 width=70) (actual time=1,689.450..2,374.821 rows=198,191 loops=1)

  • Sort Key: ec.expiration_id
  • Sort Method: external merge Disk: 9,712kB
15. 67.314 1,580.128 ↑ 49.6 198,191 1

Merge Join (cost=22,077.79..169,789.15 rows=9,824,790 width=70) (actual time=1,403.402..1,580.128 rows=198,191 loops=1)

  • Merge Cond: (oc.option_chain_id = ec.option_chain_id)
16. 13.081 134.264 ↑ 1.0 20,956 1

Sort (cost=1,930.67..1,983.25 rows=21,031 width=54) (actual time=132.045..134.264 rows=20,956 loops=1)

  • Sort Key: oc.option_chain_id
  • Sort Method: quicksort Memory: 2,406kB
17. 121.183 121.183 ↑ 1.0 20,956 1

CTE Scan on oc (cost=0.00..420.62 rows=21,031 width=54) (actual time=35.466..121.183 rows=20,956 loops=1)

18. 55.544 1,378.550 ↓ 2.1 198,191 1

Materialize (cost=20,147.12..20,614.28 rows=93,432 width=24) (actual time=1,271.322..1,378.550 rows=198,191 loops=1)

19. 144.713 1,323.006 ↓ 2.1 198,191 1

Sort (cost=20,147.12..20,380.70 rows=93,432 width=24) (actual time=1,271.317..1,323.006 rows=198,191 loops=1)

  • Sort Key: ec.option_chain_id
  • Sort Method: external merge Disk: 6,600kB
20. 194.194 1,178.293 ↓ 2.1 198,191 1

Hash Join (cost=4,208.92..10,515.54 rows=93,432 width=24) (actual time=936.273..1,178.293 rows=198,191 loops=1)

  • Hash Cond: (ec.expiration_id = ec_1.expiration_id)
21. 107.405 107.405 ↓ 1.1 198,191 1

CTE Scan on ec (cost=0.00..3,737.26 rows=186,863 width=16) (actual time=59.423..107.405 rows=198,191 loops=1)

22. 82.043 876.694 ↓ 991.0 198,191 1

Hash (cost=4,206.42..4,206.42 rows=200 width=8) (actual time=876.692..876.694 rows=198,191 loops=1)

  • Buckets: 131,072 (originally 1024) Batches: 4 (originally 1) Memory Usage: 3,073kB
23. 241.465 794.651 ↓ 991.0 198,191 1

HashAggregate (cost=4,204.42..4,206.42 rows=200 width=8) (actual time=671.917..794.651 rows=198,191 loops=1)

  • Group Key: ec_1.expiration_id
  • Batches: 5 Memory Usage: 4,161kB Disk Usage: 6,968kB
24. 553.186 553.186 ↓ 1.1 198,191 1

CTE Scan on ec ec_1 (cost=0.00..3,737.26 rows=186,863 width=8) (actual time=0.004..553.186 rows=198,191 loops=1)

Planning time : 40.770 ms
Execution time : 92,508.087 ms