explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CCmR

Settings
# exclusive inclusive rows x rows loops node
1. 14.881 734.208 ↓ 153.2 42,750 1

Nested Loop (cost=3,894.32..7,005.1 rows=279 width=97) (actual time=501.852..734.208 rows=42,750 loops=1)

  • Buffers: shared hit=202081, local hit=96870 read=2786, temp read=7167 written=7153
2. 22.911 676.577 ↓ 105.6 42,750 1

Hash Join (cost=3,894.04..6,841.11 rows=405 width=129) (actual time=501.829..676.577 rows=42,750 loops=1)

  • Buffers: shared hit=202081, local hit=19 read=2786, temp read=7167 written=7153
3. 139.228 613.966 ↓ 104.7 43,865 1

Hash Join (cost=679.59..3,620.21 rows=419 width=145) (actual time=461.504..613.966 rows=43,865 loops=1)

  • Buffers: shared hit=202081, local hit=19 read=1214, temp read=7167 written=7153
4. 13.388 13.388 ↑ 1.0 41,069 1

Seq Scan on _sales_raw s (cost=0..1,704.41 rows=41,069 width=68) (actual time=0.026..13.388 rows=41,069 loops=1)

  • Filter: (s.redistributor_company_id IS NOT NULL)
  • Buffers: local read=1185
5. 170.699 461.350 ↓ 118.7 267,505 1

Hash (cost=645.78..645.78 rows=2,254 width=143) (actual time=461.35..461.35 rows=267,505 loops=1)

  • Buffers: shared hit=202081, local hit=19 read=29, temp written=4840
6. 131.337 290.651 ↓ 118.7 267,505 1

Nested Loop (cost=209.13..645.78 rows=2,254 width=143) (actual time=4.274..290.651 rows=267,505 loops=1)

  • Buffers: shared hit=202081, local hit=19 read=29
7. 1.424 40.646 ↓ 52.9 2,697 1

Nested Loop (cost=208.71..309.66 rows=51 width=66) (actual time=4.257..40.646 rows=2,697 loops=1)

  • Buffers: shared hit=44999, local hit=19 read=29
8. 4.790 33.772 ↓ 48.7 2,725 1

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

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

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

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

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

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

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

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

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

  • Buffers: local hit=19 read=23
13. 0.140 1.156 ↓ 2.5 520 1

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

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

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

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

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

  • Buffers: shared hit=16, local read=3
16. 0.107 0.107 ↑ 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.107 rows=1,397 loops=1)

  • Buffers: shared hit=16
17. 0.131 0.188 ↑ 1.0 520 1

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

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

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

  • Buffers: local read=3
19. 0.091 0.335 ↑ 1.0 501 1

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

  • Buffers: local read=3
20. 0.186 0.244 ↑ 1.0 501 1

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

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

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

  • Buffers: local read=3
22. 12.342 12.342 ↑ 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.002..0.002 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=8147
25. 118.668 118.668 ↓ 1.8 105 2,697

Index Scan using contract_product_materialized_pkey on contract_product_materialized contract_product_materialized (cost=0.42..5.59 rows=57 width=97) (actual time=0.004..0.044 rows=105 loops=2,697)

  • Index Cond: (contract_product_materialized.contract_id = cav.contract_amend_version_id)
  • Buffers: shared hit=157082
26. 19.190 39.700 ↑ 1.0 65,698 1

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

  • Buffers: local read=1572
27. 20.510 20.510 ↑ 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.02..20.51 rows=65,698 loops=1)

  • Buffers: local read=1572
28. 42.750 42.750 ↓ 0.0 0 42,750

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,750)

  • Index Cond: (xx.company_id = s.redistributor_company_id)
  • Buffers: local hit=96851
Planning time : 66.047 ms
Execution time : 737.253 ms