explain.depesz.com

PostgreSQL's explain analyze made readable

Result: tQe7 : Optimization for: Optimization for: Optimization for: plan #ykf1; plan #Kkls; plan #hRGP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 1,029.578 118,874.489 ↓ 0.0 0 1

Insert on t_sales_potential_earnings (cost=189.31..28,950.03 rows=3 width=314) (actual time=118,874.489..118,874.489 rows=0 loops=1)

  • Buffers: shared hit=827607 read=8, local hit=57866287 read=25052491 dirtied=113484 written=113480
2. 180.373 117,844.911 ↓ 55,691.7 167,075 1

Nested Loop (cost=189.31..28,950.03 rows=3 width=314) (actual time=4.034..117,844.911 rows=167,075 loops=1)

  • Buffers: shared hit=827607 read=8, local hit=57238614 read=25018911 written=113077
3. 370.406 2,716.938 ↓ 41,768.8 167,075 1

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

  • Buffers: shared hit=827607 read=8, local hit=11 read=13628 written=706
4. 187.375 1,478.908 ↓ 16,685.1 216,906 1

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

  • Buffers: local hit=11 read=13628 written=706
5. 721.942 1,289.745 ↓ 16,685.1 216,906 1

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

  • Buffers: local hit=11 read=13608 written=706
6. 306.290 565.918 ↓ 157.0 1,009,732 1

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

  • Buffers: local hit=11 read=13583 written=706
7. 259.402 259.402 ↓ 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.015..259.402 rows=193,501 loops=1)

  • Filter: (s.company_grouping_id IS NOT NULL)
  • Buffers: local read=13583 written=706
8. 0.015 0.226 ↑ 1.0 30 1

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

  • Buffers: local hit=11
9. 0.211 0.211 ↑ 1.0 30 1

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

  • Filter: (cf.parent_company_id IS NOT NULL)
  • Buffers: local hit=11
10. 1.007 1.885 ↑ 1.0 2,656 1

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

  • Buffers: local read=25
11. 0.878 0.878 ↑ 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.013..0.878 rows=2,656 loops=1)

  • Buffers: local read=25
12. 0.968 1.788 ↑ 1.0 2,656 1

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

  • Buffers: local read=20
13. 0.820 0.820 ↑ 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.024..0.82 rows=2,656 loops=1)

  • Buffers: local read=20
14. 867.624 867.624 ↑ 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.004..0.004 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=827607 read=8
15. 114,947.600 114,947.600 ↓ 0.0 0 167,075

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

  • Index Cond: (x.contract_item_id = contract_product_materialized.contract_item_id)
  • Heap Fetches: 101627973
  • Buffers: local hit=57238603 read=25005283 written=112371
Planning time : 3.957 ms
Execution time : 118,874.654 ms