explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vii3

Settings
# exclusive inclusive rows x rows loops node
1. 4.312 606.186 ↓ 153.3 42,761 1

Nested Loop (cost=3,894.32..7,005.1 rows=279 width=172) (actual time=411.209..606.186 rows=42,761 loops=1)

  • Buffers: shared hit=202101, local hit=96892 read=2786, temp read=4975 written=4961
2. 19.432 559.113 ↓ 105.6 42,761 1

Hash Join (cost=3,894.04..6,841.11 rows=405 width=139) (actual time=411.188..559.113 rows=42,761 loops=1)

  • Buffers: shared hit=202101, local hit=19 read=2786, temp read=4975 written=4961
3. 116.042 500.740 ↓ 104.7 43,876 1

Hash Join (cost=679.59..3,620.21 rows=419 width=139) (actual time=371.606..500.74 rows=43,876 loops=1)

  • Buffers: shared hit=202101, local hit=19 read=1214, temp read=4975 written=4961
4. 13.254 13.254 ↑ 1.0 41,069 1

Seq Scan on _sales_raw s (cost=0..1,704.41 rows=41,069 width=107) (actual time=0.027..13.254 rows=41,069 loops=1)

  • Filter: (s.redistributor_company_id IS NOT NULL)
  • Buffers: local read=1185
5. 118.343 371.444 ↓ 118.9 267,893 1

Hash (cost=645.78..645.78 rows=2,254 width=78) (actual time=371.444..371.444 rows=267,893 loops=1)

  • Buffers: shared hit=202101, local hit=19 read=29, temp written=2796
6. 105.390 253.101 ↓ 118.9 267,893 1

Nested Loop (cost=209.13..645.78 rows=2,254 width=78) (actual time=3.769..253.101 rows=267,893 loops=1)

  • Buffers: shared hit=202101, local hit=19 read=29
7. 0.572 37.052 ↓ 52.9 2,699 1

Nested Loop (cost=208.71..309.66 rows=51 width=66) (actual time=3.754..37.052 rows=2,699 loops=1)

  • Buffers: shared hit=45001, local hit=19 read=29
8. 3.979 31.030 ↓ 48.7 2,725 1

Nested Loop (cost=208.44..290.19 rows=56 width=82) (actual time=3.744..31.03 rows=2,725 loops=1)

  • Buffers: shared hit=36852, local hit=19 read=29
9. 6.272 20.880 ↓ 76.2 6,171 1

Nested Loop (cost=208.17..256.76 rows=81 width=98) (actual time=3.735..20.88 rows=6,171 loops=1)

  • Buffers: shared hit=18661, local hit=19 read=29
10. 2.935 8.437 ↓ 76.2 6,171 1

Hash Join (cost=207.89..229.64 rows=81 width=110) (actual time=3.717..8.437 rows=6,171 loops=1)

  • Buffers: shared hit=16, local hit=19 read=29
11. 3.757 4.374 ↓ 4.0 5,645 1

HashAggregate (cost=126.68..140.84 rows=1,416 width=32) (actual time=2.576..4.374 rows=5,645 loops=1)

  • Group Key: cagg.contract_family_id, cagg.company_id
  • Buffers: local hit=19 read=23
12. 0.617 0.617 ↑ 1.0 5,645 1

Seq Scan on _contractee_agg cagg (cost=0..98.45 rows=5,645 width=32) (actual time=0.006..0.617 rows=5,645 loops=1)

  • Buffers: local hit=19 read=23
13. 0.145 1.128 ↓ 2.5 520 1

Hash (cost=78.66..78.66 rows=205 width=78) (actual time=1.128..1.128 rows=520 loops=1)

  • Buffers: shared hit=16, local read=6
14. 0.131 0.983 ↓ 2.5 520 1

Hash Join (cost=35.23..78.66 rows=205 width=78) (actual time=0.51..0.983 rows=520 loops=1)

  • Buffers: shared hit=16, local read=6
15. 0.262 0.506 ↑ 1.0 520 1

Hash Join (cost=14.7..54.12 rows=520 width=62) (actual time=0.153..0.506 rows=520 loops=1)

  • Buffers: shared hit=16, local read=3
16. 0.111 0.111 ↑ 1.0 1,397 1

Seq Scan on contract_renew_version crv (cost=0..29.97 rows=1,397 width=46) (actual time=0.006..0.111 rows=1,397 loops=1)

  • Buffers: shared hit=16
17. 0.078 0.133 ↑ 1.0 520 1

Hash (cost=8.2..8.2 rows=520 width=16) (actual time=0.133..0.133 rows=520 loops=1)

  • Buffers: local read=3
18. 0.055 0.055 ↑ 1.0 520 1

Seq Scan on _contract_renew_version xcrv (cost=0..8.2 rows=520 width=16) (actual time=0.009..0.055 rows=520 loops=1)

  • Buffers: local read=3
19. 0.087 0.346 ↑ 1.0 501 1

Hash (cost=14.27..14.27 rows=501 width=16) (actual time=0.346..0.346 rows=501 loops=1)

  • Buffers: local read=3
20. 0.202 0.259 ↑ 1.0 501 1

HashAggregate (cost=9.26..14.27 rows=501 width=16) (actual time=0.179..0.259 rows=501 loops=1)

  • Group Key: xcf.contract_family_id
  • Buffers: local read=3
21. 0.057 0.057 ↑ 1.0 501 1

Seq Scan on _contract_family xcf (cost=0..8.01 rows=501 width=16) (actual time=0.011..0.057 rows=501 loops=1)

  • Buffers: local read=3
22. 6.171 6.171 ↑ 1.0 1 6,171

Index Scan using contract_family_pkey on contract_family cf (cost=0.28..0.33 rows=1 width=36) (actual time=0.001..0.001 rows=1 loops=6,171)

  • Index Cond: (cf.contract_family_id = crv.contract_family_id)
  • Buffers: shared hit=18645
23. 6.171 6.171 ↑ 1.0 1 6,171

Index Scan using grouping_company_pkey on grouping_company gc (cost=0.27..0.41 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=6,171)

  • Index Cond: (gc.grouping_id = cf.contractee_grouping_id)
  • Filter: (gc.grouping_company_type = 'BUYING_GROUP'::enum.grouping_company_type)
  • Buffers: shared hit=18191
24. 5.450 5.450 ↑ 1.0 1 2,725

Index Scan using contract_amend_version_contract_renew_version_id_idx on contract_amend_version cav (cost=0.28..0.35 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=2,725)

  • Index Cond: (cav.contract_renew_version_id = crv.contract_renew_version_id)
  • Buffers: shared hit=8149
25. 110.659 110.659 ↓ 1.8 105 2,699

Index Scan using contract_product_materialized_pkey on contract_product_materialized contract_product_materialized (cost=0.42..5.59 rows=57 width=52) (actual time=0.004..0.041 rows=105 loops=2,699)

  • Index Cond: (contract_product_materialized.contract_id = cav.contract_amend_version_id)
  • Buffers: shared hit=157100
26. 18.443 38.941 ↑ 1.0 65,698 1

Hash (cost=2,228.98..2,228.98 rows=65,698 width=32) (actual time=38.941..38.941 rows=65,698 loops=1)

  • Buffers: local read=1572
27. 20.498 20.498 ↑ 1.0 65,698 1

Seq Scan on _sales_potential_earnings x (cost=0..2,228.98 rows=65,698 width=32) (actual time=0.023..20.498 rows=65,698 loops=1)

  • Buffers: local read=1572
28. 42.761 42.761 ↓ 0.0 0 42,761

Index Scan using ix_tmp_contractee_agg_company on _contractee_agg xx (cost=0.28..0.35 rows=4 width=32) (actual time=0.001..0.001 rows=0 loops=42,761)

  • Index Cond: (xx.company_id = s.redistributor_company_id)
  • Buffers: local hit=96873
Planning time : 54.006 ms
Execution time : 608.838 ms