explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XlG9

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 6,306.188 ↓ 15.5 31 1

Unique (cost=671,974.98..671,974.99 rows=2 width=98) (actual time=6,306.166..6,306.188 rows=31 loops=1)

  • Buffers: shared hit=170031, temp read=26903 written=26903
2.          

CTE poly

3. 0.003 0.003 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.003 rows=1 loops=1)

4.          

CTE cte

5. 0.508 8.232 ↑ 5.3 118 1

Unique (cost=800.70..808.49 rows=623 width=32) (actual time=6.080..8.232 rows=118 loops=1)

  • Buffers: shared hit=115
6. 1.098 7.724 ↓ 4.0 2,514 1

Group (cost=800.70..806.93 rows=623 width=32) (actual time=6.079..7.724 rows=2,514 loops=1)

  • Group Key: pd_1.unit_building_id, pd_1.coverage_effective_date, pd_1.batch_date
  • Buffers: shared hit=115
7. 2.159 6.626 ↓ 4.0 2,514 1

Sort (cost=800.70..802.26 rows=623 width=32) (actual time=6.077..6.626 rows=2,514 loops=1)

  • Sort Key: pd_1.unit_building_id, pd_1.coverage_effective_date DESC, pd_1.batch_date DESC
  • Sort Method: quicksort Memory: 293kB
  • Buffers: shared hit=115
8. 0.979 4.467 ↓ 4.0 2,514 1

Nested Loop (cost=13.01..771.78 rows=623 width=32) (actual time=0.429..4.467 rows=2,514 loops=1)

  • Buffers: shared hit=115
9. 0.005 0.005 ↑ 1.0 1 1

CTE Scan on poly (cost=0.00..0.02 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1)

10. 3.100 3.483 ↓ 40.5 2,514 1

Bitmap Heap Scan on policy_data pd_1 (cost=13.01..771.14 rows=62 width=64) (actual time=0.419..3.483 rows=2,514 loops=1)

  • Recheck Cond: (poly.geom && point_geom_4326)
  • Filter: (((building_tiv IS NOT NULL) OR (business_income_tiv IS NOT NULL) OR (contents_tiv IS NOT NULL)) AND (coverage_effective_date <= 2019-09-04 06:00:11.598+00::timestamp with time zone) AND (internal_company_id = 14) AND _st_intersects(poly.geom, point_geom_4326))
  • Heap Blocks: exact=92
  • Buffers: shared hit=115
11. 0.383 0.383 ↓ 7.3 2,514 1

Bitmap Index Scan on policy_data_geometry_gix (cost=0.00..12.99 rows=344 width=0) (actual time=0.383..0.383 rows=2,514 loops=1)

  • Index Cond: (poly.geom && point_geom_4326)
  • Buffers: shared hit=23
12.          

CTE cte2

13. 0.002 1,268.430 ↓ 0.0 0 1

Unique (cost=170,883.04..175,121.22 rows=339,054 width=32) (actual time=1,268.429..1,268.430 rows=0 loops=1)

  • Buffers: shared hit=84958
14. 0.001 1,268.428 ↓ 0.0 0 1

Group (cost=170,883.04..174,273.58 rows=339,054 width=32) (actual time=1,268.428..1,268.428 rows=0 loops=1)

  • Group Key: pd_2.unit_building_id, pd_2.coverage_effective_date, pd_2.batch_date
  • Buffers: shared hit=84958
15. 0.007 1,268.427 ↓ 0.0 0 1

Sort (cost=170,883.04..171,730.68 rows=339,054 width=32) (actual time=1,268.427..1,268.427 rows=0 loops=1)

  • Sort Key: pd_2.unit_building_id, pd_2.coverage_effective_date DESC, pd_2.batch_date DESC
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=84958
16. 53.972 1,268.420 ↓ 0.0 0 1

Nested Loop (cost=18.52..139,738.97 rows=339,054 width=32) (actual time=1,268.419..1,268.420 rows=0 loops=1)

  • Join Filter: st_disjoint(poly_1.geom, pd_2.point_geom_4326)
  • Rows Removed by Join Filter: 2410
  • Buffers: shared hit=84958
17. 0.003 0.003 ↑ 1.0 1 1

CTE Scan on poly poly_1 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.003 rows=1 loops=1)

18. 437.218 1,214.445 ↑ 422.1 2,410 1

Hash Join (cost=18.52..127,024.45 rows=1,017,160 width=64) (actual time=92.328..1,214.445 rows=2,410 loops=1)

  • Hash Cond: (pd_2.unit_building_id = cte.unit_building_id)
  • Buffers: shared hit=84958
19. 777.094 777.094 ↑ 1.0 2,030,708 1

Seq Scan on policy_data pd_2 (cost=0.00..119,337.02 rows=2,034,321 width=64) (actual time=0.005..777.094 rows=2,030,708 loops=1)

  • Filter: active
  • Rows Removed by Filter: 1422031
  • Buffers: shared hit=84958
20. 0.034 0.133 ↑ 1.7 118 1

Hash (cost=16.02..16.02 rows=200 width=16) (actual time=0.133..0.133 rows=118 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
21. 0.068 0.099 ↑ 1.7 118 1

HashAggregate (cost=14.02..16.02 rows=200 width=16) (actual time=0.074..0.099 rows=118 loops=1)

  • Group Key: cte.unit_building_id
22. 0.031 0.031 ↑ 5.3 118 1

CTE Scan on cte (cost=0.00..12.46 rows=623 width=16) (actual time=0.001..0.031 rows=118 loops=1)

23.          

CTE combined

24. 0.072 1,276.823 ↑ 1,034.3 118 1

Hash Full Join (cost=20.25..49,767.94 rows=122,044 width=32) (actual time=1,276.773..1,276.823 rows=118 loops=1)

  • Hash Cond: (cte2.unit_building_id = cte_1.unit_building_id)
  • Filter: (((cte_1.coverage_effective_date >= cte2.coverage_effective_date) AND (cte_1.batch_date >= cte2.batch_date)) OR (cte2.unit_building_id IS NULL))
  • Buffers: shared hit=85073
25. 1,268.430 1,268.430 ↓ 0.0 0 1

CTE Scan on cte2 (cost=0.00..6,781.08 rows=339,054 width=32) (actual time=1,268.429..1,268.430 rows=0 loops=1)

  • Buffers: shared hit=84958
26. 0.041 8.321 ↑ 5.3 118 1

Hash (cost=12.46..12.46 rows=623 width=32) (actual time=8.321..8.321 rows=118 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=115
27. 8.280 8.280 ↑ 5.3 118 1

CTE Scan on cte cte_1 (cost=0.00..12.46 rows=623 width=32) (actual time=6.082..8.280 rows=118 loops=1)

  • Buffers: shared hit=115
28. 0.090 6,306.170 ↓ 15.5 31 1

Sort (cost=446,277.33..446,277.33 rows=2 width=98) (actual time=6,306.164..6,306.170 rows=31 loops=1)

  • Sort Key: pd.unit_building_id, pd.prop_detail_key DESC, pd.coverage_effective_date DESC, pd.batch_date DESC
  • Sort Method: quicksort Memory: 29kB
  • Buffers: shared hit=170031, temp read=26903 written=26903
29. 582.410 6,306.080 ↓ 15.5 31 1

Merge Join (cost=424,713.65..446,277.32 rows=2 width=98) (actual time=4,687.258..6,306.080 rows=31 loops=1)

  • Merge Cond: ((pd.unit_building_id = combined.unit_building_id) AND (pd.coverage_effective_date = combined.coverage_effective_date) AND (pd.batch_date = combined.batch_date))
  • Buffers: shared hit=170031, temp read=26903 written=26903
30. 3,433.748 4,446.661 ↑ 1.0 2,000,517 1

Sort (cost=411,961.85..417,047.66 rows=2,034,321 width=98) (actual time=3,397.768..4,446.661 rows=2,000,517 loops=1)

  • Sort Key: pd.unit_building_id, pd.coverage_effective_date, pd.batch_date
  • Sort Method: external merge Disk: 215056kB
  • Buffers: shared hit=84958, temp read=26903 written=26903
31. 1,012.913 1,012.913 ↑ 1.0 2,030,708 1

Seq Scan on policy_data pd (cost=0.00..119,337.02 rows=2,034,321 width=98) (actual time=0.006..1,012.913 rows=2,030,708 loops=1)

  • Filter: active
  • Rows Removed by Filter: 1422031
  • Buffers: shared hit=84958
32. 0.120 1,277.009 ↑ 1,034.3 118 1

Sort (cost=12,751.79..13,056.90 rows=122,044 width=32) (actual time=1,276.950..1,277.009 rows=118 loops=1)

  • Sort Key: combined.unit_building_id, combined.coverage_effective_date, combined.batch_date
  • Sort Method: quicksort Memory: 34kB
  • Buffers: shared hit=85073
33. 1,276.889 1,276.889 ↑ 1,034.3 118 1

CTE Scan on combined (cost=0.00..2,440.88 rows=122,044 width=32) (actual time=1,276.776..1,276.889 rows=118 loops=1)

  • Buffers: shared hit=85073
Planning time : 0.649 ms
Execution time : 6,345.808 ms