explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GWqc

Settings
# exclusive inclusive rows x rows loops node
1. 6.143 680.286 ↓ 153.3 42,761 1

Nested Loop (cost=3,894.32..7,005.1 rows=279 width=97) (actual time=459.134..680.286 rows=42,761 loops=1)

  • Buffers: shared hit=202101, local hit=96886 read=2792, temp read=7174 written=7160
2. 21.698 631.382 ↓ 105.6 42,761 1

Hash Join (cost=3,894.04..6,841.11 rows=405 width=129) (actual time=459.109..631.382 rows=42,761 loops=1)

  • Buffers: shared hit=202101, local hit=19 read=2786, temp read=7174 written=7160
3. 138.235 571.054 ↓ 104.7 43,876 1

Hash Join (cost=679.59..3,620.21 rows=419 width=145) (actual time=419.829..571.054 rows=43,876 loops=1)

  • Buffers: shared hit=202101, local hit=19 read=1214, temp read=7174 written=7160
4. 13.621 13.621 ↑ 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.621 rows=41,069 loops=1)

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

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

  • Buffers: shared hit=202101, local hit=19 read=29, temp written=4849
6. 119.347 265.467 ↓ 118.9 267,893 1

Nested Loop (cost=209.13..645.78 rows=2,254 width=143) (actual time=3.937..265.467 rows=267,893 loops=1)

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

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

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

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

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

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

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

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

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

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

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

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

  • Buffers: local hit=19 read=23
13. 0.150 1.176 ↓ 2.5 520 1

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

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

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

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

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

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

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

  • Buffers: shared hit=16
17. 0.098 0.171 ↑ 1.0 520 1

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

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

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

  • Buffers: local read=3
19. 0.086 0.334 ↑ 1.0 501 1

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

  • Buffers: local read=3
20. 0.190 0.248 ↑ 1.0 501 1

HashAggregate (cost=9.26..14.27 rows=501 width=16) (actual time=0.18..0.248 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=8149
25. 107.960 107.960 ↓ 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=97) (actual time=0.004..0.04 rows=105 loops=2,699)

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

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

  • Buffers: local read=1572
27. 19.979 19.979 ↑ 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.022..19.979 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=96867 read=6
Planning time : 63.35 ms
Execution time : 683.022 ms