explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2NoX

Settings
# exclusive inclusive rows x rows loops node
1. 476.877 2,413.580 ↓ 0.0 0 1

Insert on t_sales_potential_earnings (cost=189.58..28,951.18 rows=3 width=314) (actual time=2,413.58..2,413.58 rows=0 loops=1)

  • Buffers: shared hit=827,599 read=16, local hit=1,267,384 read=25,480 dirtied=11,846 written=11,119
2. 148.099 1,936.703 ↓ 55,691.7 167,075 1

Nested Loop (cost=189.58..28,951.18 rows=3 width=314) (actual time=6.624..1,936.703 rows=167,075 loops=1)

  • Buffers: shared hit=827,599 read=16, local hit=589,529 read=20,801 dirtied=5,300 written=9,044
3. 132.258 1,454.454 ↓ 41,768.8 167,075 1

Nested Loop (cost=189.16..28,949.31 rows=4 width=157) (actual time=6.55..1,454.454 rows=167,075 loops=1)

  • Buffers: shared hit=827,599 read=16, local read=13,639 written=5,992
4. 71.896 888.384 ↓ 16,685.1 216,906 1

Hash Join (cost=188.74..28,906.96 rows=13 width=161) (actual time=6.461..888.384 rows=216,906 loops=1)

  • Buffers: local read=13,639 written=5,992
5. 406.889 813.465 ↓ 16,685.1 216,906 1

Hash Join (cost=108.98..28,827.02 rows=13 width=161) (actual time=3.418..813.465 rows=216,906 loops=1)

  • Buffers: local read=13,619 written=5,992
6. 211.887 403.620 ↓ 157.0 1,009,732 1

Hash Join (cost=24.21..28,479.46 rows=6,432 width=145) (actual time=0.439..403.62 rows=1,009,732 loops=1)

  • Buffers: local read=13,594 written=5,992
7. 191.368 191.368 ↓ 1.0 193,501 1

Seq Scan on t_sales_raw s (cost=0..20,658.21 rows=193,318 width=121) (actual time=0.053..191.368 rows=193,501 loops=1)

  • Filter: (s.company_grouping_id IS NOT NULL)
  • Buffers: local read=13,583 written=5,992
8. 0.023 0.365 ↑ 1.0 30 1

Hash (cost=23.84..23.84 rows=30 width=56) (actual time=0.364..0.365 rows=30 loops=1)

  • Buffers: local read=11
9. 0.342 0.342 ↑ 1.0 30 1

Seq Scan on t_contract_family cf (cost=0..23.84 rows=30 width=56) (actual time=0.054..0.342 rows=30 loops=1)

  • Filter: (cf.parent_company_id IS NOT NULL)
  • Buffers: local read=11
10. 1.562 2.956 ↑ 1.0 2,656 1

Hash (cost=51.56..51.56 rows=2,656 width=46) (actual time=2.955..2.956 rows=2,656 loops=1)

  • Buffers: local read=25
11. 1.394 1.394 ↑ 1.0 2,656 1

Seq Scan on t_contract_renew_version crv (cost=0..51.56 rows=2,656 width=46) (actual time=0.015..1.394 rows=2,656 loops=1)

  • Buffers: local read=25
12. 1.670 3.023 ↑ 1.0 2,656 1

Hash (cost=46.56..46.56 rows=2,656 width=32) (actual time=3.023..3.023 rows=2,656 loops=1)

  • Buffers: local read=20
13. 1.353 1.353 ↑ 1.0 2,656 1

Seq Scan on t_contract_amend_version cav (cost=0..46.56 rows=2,656 width=32) (actual time=0.045..1.353 rows=2,656 loops=1)

  • Buffers: local read=20
14. 433.812 433.812 ↑ 1.0 1 216,906

Index Scan using contract_product_materialized_pkey on contract_product_materialized contract_product_materialized (cost=0.42..3.24 rows=1 width=52) (actual time=0.002..0.002 rows=1 loops=216,906)

  • Index Cond: ((contract_product_materialized.contract_id = cav.contract_amend_version_id) AND (contract_product_materialized.product_id = s.product_id))
  • Buffers: shared hit=827,599 read=16
15. 334.150 334.150 ↓ 0.0 0 167,075

Index Only Scan using idx_spe_unique on t_sales_potential_earnings x (cost=0.41..0.46 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=167,075)

  • Index Cond: ((x.contract_item_id = contract_product_materialized.contract_item_id) AND (x.sale_id = s.sale_id))
  • Heap Fetches: 94,060
  • Buffers: local hit=589,529 read=7,162 dirtied=5,300 written=3,052
Planning time : 3.945 ms
Execution time : 2,413.818 ms