explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7kOs : Optimization for: Optimization for: Optimization for: plan #ykf1; plan #Kkls; plan #hRGP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 15.322 134.974 ↓ 20,132.0 20,132 1

Nested Loop (cost=35.81..3,921.71 rows=1 width=198) (actual time=1.11..134.974 rows=20,132 loops=1)

  • Buffers: shared hit=100321, local hit=60394 read=1201
2. 5.144 99.520 ↓ 20,132.0 20,132 1

Nested Loop (cost=35.4..3,921.17 rows=1 width=152) (actual time=1.084..99.52 rows=20,132 loops=1)

  • Buffers: shared hit=100321, local read=1199
3. 25.377 41.308 ↓ 3,790.6 26,534 1

Hash Join (cost=34.98..3,899.59 rows=7 width=156) (actual time=1.043..41.308 rows=26,534 loops=1)

  • Buffers: local read=1199
4. 14.940 14.940 ↑ 1.0 51,924 1

Seq Scan on t_sales_raw s (cost=0..1,704.41 rows=51,924 width=116) (actual time=0.026..14.94 rows=51,924 loops=1)

  • Filter: (s.company_grouping_id IS NOT NULL)
  • Buffers: local read=1185
5. 0.030 0.991 ↓ 1.1 34 1

Hash (cost=34.61..34.61 rows=30 width=86) (actual time=0.991..0.991 rows=34 loops=1)

  • Buffers: local read=14
6. 0.203 0.961 ↓ 1.1 34 1

Hash Join (cost=23.38..34.61 rows=30 width=86) (actual time=0.629..0.961 rows=34 loops=1)

  • Buffers: local read=14
7. 0.171 0.171 ↑ 1.0 504 1

Seq Scan on t_contract_amend_version cav (cost=0..9.04 rows=504 width=32) (actual time=0.028..0.171 rows=504 loops=1)

  • Buffers: local read=4
8. 0.029 0.587 ↓ 1.1 35 1

Hash (cost=22.99..22.99 rows=31 width=86) (actual time=0.587..0.587 rows=35 loops=1)

  • Buffers: local read=10
9. 0.211 0.558 ↓ 1.1 35 1

Hash Join (cost=10.45..22.99 rows=31 width=86) (actual time=0.227..0.558 rows=35 loops=1)

  • Buffers: local read=10
10. 0.168 0.168 ↑ 1.0 526 1

Seq Scan on t_contract_renew_version crv (cost=0..10.26 rows=526 width=46) (actual time=0.039..0.168 rows=526 loops=1)

  • Buffers: local read=5
11. 0.023 0.179 ↑ 1.0 30 1

Hash (cost=10.07..10.07 rows=30 width=56) (actual time=0.178..0.179 rows=30 loops=1)

  • Buffers: local read=5
12. 0.156 0.156 ↑ 1.0 30 1

Seq Scan on t_contract_family cf (cost=0..10.07 rows=30 width=56) (actual time=0.022..0.156 rows=30 loops=1)

  • Filter: (cf.parent_company_id IS NOT NULL)
  • Buffers: local read=5
13. 53.068 53.068 ↑ 1.0 1 26,534

Index Scan using contract_product_materialized_pkey on contract_product_materialized contract_product_materialized (cost=0.42..3.07 rows=1 width=52) (actual time=0.002..0.002 rows=1 loops=26,534)

  • Index Cond: ((contract_product_materialized.contract_id = cav.contract_amend_version_id) AND (contract_product_materialized.product_id = s.product_id))
  • Buffers: shared hit=100321
14. 20.132 20.132 ↓ 0.0 0 20,132

Index Only Scan using idx_spe_unique on t_sales_potential_earnings x (cost=0.41..0.47 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=20,132)

  • Index Cond: ((x.contract_item_id = contract_product_materialized.contract_item_id) AND (x.sale_id = s.sale_id))
  • Buffers: local hit=60394 read=2
Planning time : 6.903 ms
Execution time : 136.813 ms