explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 0ExX

Settings
# exclusive inclusive rows x rows loops node
1. 196,627.720 676,098.813 ↓ 0.0 0 1

Insert on fact_deal_line (cost=20,394.35..4,268,945.74 rows=7,487,387 width=234) (actual time=676,098.813..676,098.813 rows=0 loops=1)

2. 56,407.365 479,471.093 ↓ 2.0 14,738,864 1

Subquery Scan on *SELECT* (cost=20,394.35..4,268,945.74 rows=7,487,387 width=234) (actual time=247.199..479,471.093 rows=14,738,864 loops=1)

3. 422,124.262 423,063.728 ↓ 2.0 14,738,864 1

Hash Left Join (cost=20,394.35..4,063,042.60 rows=7,487,387 width=184) (actual time=247.177..423,063.728 rows=14,738,864 loops=1)

  • Hash Cond: (lh.broadcast_quarter = (dd.broadcast_quarter_display)::bpchar)
4. 143.894 930.395 ↓ 1.9 160,935 1

Hash Left Join (cost=19,100.24..27,666.72 rows=82,677 width=263) (actual time=237.851..930.395 rows=160,935 loops=1)

  • Hash Cond: ((lh.network_cd)::text = (dn.network_cd)::text)
5. 195.961 786.379 ↓ 1.9 160,935 1

Hash Left Join (cost=19,087.66..26,517.33 rows=82,677 width=263) (actual time=237.720..786.379 rows=160,935 loops=1)

  • Hash Cond: ((lh.deal_cd)::text = (dp.deal_cd)::text)
6. 328.391 579.372 ↓ 1.9 160,935 1

Hash Right Join (cost=17,990.90..24,283.75 rows=82,677 width=255) (actual time=226.571..579.372 rows=160,935 loops=1)

  • Hash Cond: ((cl.deal_cd)::text = (lh.deal_cd)::text)
7. 24.613 24.613 ↓ 4.6 68,742 1

Seq Scan on temp_change_log_entries cl (cost=0.00..1,035.45 rows=15,045 width=222) (actual time=0.020..24.613 rows=68,742 loops=1)

8. 87.140 226.368 ↓ 2.0 160,935 1

Hash (cost=14,161.75..14,161.75 rows=82,012 width=251) (actual time=226.368..226.368 rows=160,935 loops=1)

  • Buckets: 16384 (originally 16384) Batches: 16 (originally 8) Memory Usage: 4033kB
9. 66.436 139.228 ↓ 2.0 160,935 1

Hash Join (cost=8,530.13..14,161.75 rows=82,012 width=251) (actual time=65.127..139.228 rows=160,935 loops=1)

  • Hash Cond: ((lhd.deal_cd)::text = (lh.deal_cd)::text)
10. 7.957 7.957 ↓ 2.4 18,489 1

Seq Scan on lighthouse_deals lhd (cost=0.00..583.05 rows=7,605 width=354) (actual time=0.014..7.957 rows=18,489 loops=1)

11. 38.081 64.835 ↑ 1.0 160,939 1

Hash (cost=3,688.39..3,688.39 rows=160,939 width=115) (actual time=64.834..64.835 rows=160,939 loops=1)

  • Buckets: 32768 Batches: 8 Memory Usage: 1705kB
12. 26.754 26.754 ↑ 1.0 160,939 1

Seq Scan on deal_network_quarter lh (cost=0.00..3,688.39 rows=160,939 width=115) (actual time=0.133..26.754 rows=160,939 loops=1)

13. 3.595 11.046 ↑ 1.0 18,490 1

Hash (cost=865.64..865.64 rows=18,490 width=20) (actual time=11.046..11.046 rows=18,490 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1133kB
14. 4.148 7.451 ↑ 1.0 18,490 1

Hash Left Join (cost=290.17..865.64 rows=18,490 width=20) (actual time=2.109..7.451 rows=18,490 loops=1)

  • Hash Cond: (dp.planner_user_id = du.user_id)
15. 1.356 1.356 ↑ 1.0 18,490 1

Seq Scan on deal_properties dp (cost=0.00..526.90 rows=18,490 width=20) (actual time=0.131..1.356 rows=18,490 loops=1)

16. 0.984 1.947 ↓ 1.0 6,764 1

Hash (cost=205.63..205.63 rows=6,763 width=8) (actual time=1.947..1.947 rows=6,764 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 329kB
17. 0.963 0.963 ↓ 1.0 6,764 1

Seq Scan on dim_user du (cost=0.00..205.63 rows=6,763 width=8) (actual time=0.005..0.963 rows=6,764 loops=1)

18. 0.065 0.122 ↑ 1.0 337 1

Hash (cost=8.37..8.37 rows=337 width=14) (actual time=0.122..0.122 rows=337 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
19. 0.057 0.057 ↑ 1.0 337 1

Seq Scan on dim_network dn (cost=0.00..8.37 rows=337 width=14) (actual time=0.006..0.057 rows=337 loops=1)

20. 3.535 9.071 ↑ 1.0 21,916 1

Hash (cost=1,020.16..1,020.16 rows=21,916 width=15) (actual time=9.071..9.071 rows=21,916 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1262kB
21. 5.536 5.536 ↑ 1.0 21,916 1

Seq Scan on dim_dates dd (cost=0.00..1,020.16 rows=21,916 width=15) (actual time=0.122..5.536 rows=21,916 loops=1)

Planning time : 1.309 ms
Execution time : 676,100.060 ms