explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lSrB : boom

Settings
# exclusive inclusive rows x rows loops node
1. 38,073.136 38,073.136 ↑ 17.0 1 1

CTE Scan on cluster_centres cluster_centres (cost=35,269.84..35,312.68 rows=17 width=76) (actual time=38,073.134..38,073.136 rows=1 loops=1)

  • Buffers: shared hit=550173, temp read=507 written=814
2.          

CTE ll

3. 16.088 2,188.839 ↓ 2,445.4 41,572 1

Unique (cost=35,268.39..35,268.48 rows=17 width=54) (actual time=2,100.45..2,188.839 rows=41,572 loops=1)

  • Buffers: shared hit=550173, temp read=507 written=509
4. 553.248 2,172.751 ↓ 4,025.1 68,427 1

Sort (cost=35,268.39..35,268.44 rows=17 width=54) (actual time=2,100.448..2,172.751 rows=68,427 loops=1)

  • Sort Key: loan.loan_identifier, real_estate.title_number
  • Sort Method: external merge Disk: 4056kB
  • Buffers: shared hit=550173, temp read=507 written=509
5. 0.000 1,619.503 ↓ 4,025.1 68,427 1

Gather (cost=5,825.48..35,268.05 rows=17 width=54) (actual time=141.164..1,619.503 rows=68,427 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=550173
6. 28.237 1,648.278 ↓ 3,258.4 22,809 3 / 3

Nested Loop (cost=4,825.48..34,266.35 rows=7 width=54) (actual time=119.012..1,648.278 rows=22,809 loops=3)

  • Buffers: shared hit=550173
7. 51.259 1,043.144 ↓ 4,437.6 22,188 3 / 3

Nested Loop (cost=4,825.06..34,263.45 rows=5 width=67) (actual time=118.895..1,043.144 rows=22,188 loops=3)

  • Buffers: shared hit=292687
8. 198.008 369.644 ↓ 1.4 23,932 3 / 3

Hash Join (cost=4,824.63..10,808.06 rows=16,714 width=34) (actual time=118.829..369.644 rows=23,932 loops=3)

  • Buffers: shared hit=5238
9. 58.224 58.224 ↑ 1.3 172,848 3 / 3

Seq Scan on real_estate_loan real_estate_loan (cost=0..5,415.93 rows=216,193 width=21) (actual time=0.02..58.224 rows=172,848 loops=3)

  • Buffers: shared hit=3254
10. 66.328 113.412 ↑ 1.2 40,829 3 / 3

Hash (cost=4,199.58..4,199.58 rows=50,004 width=13) (actual time=113.412..113.412 rows=40,829 loops=3)

  • Buffers: shared hit=1924
11. 47.084 47.084 ↑ 1.2 40,829 3 / 3

Index Scan using loan_loan_created_on_idx on loan loan (cost=0.43..4,199.58 rows=50,004 width=13) (actual time=0.037..47.084 rows=40,829 loops=3)

  • Index Cond: ((loan.loan_created_on > '2018-01-18'::date) AND (loan.loan_created_on < '2019-05-18'::date))
  • Buffers: shared hit=1924
12. 622.241 622.241 ↑ 1.0 1 71,797 / 3

Index Scan using real_estate_title_number_idx on real_estate real_estate (cost=0.43..1.4 rows=1 width=41) (actual time=0.026..0.026 rows=1 loops=71,797)

  • Index Cond: ((real_estate.title_number)::text = (real_estate_loan.title_number)::text)
  • Filter: (('0103000020E6100000010000000500000022891B64E6491EC0AD11E246E1FA4840A267FAD58CE7FA3FAD11E246E1FA4840A267FAD58CE7FA3F637BFDAE47514D4022891B64E6491EC0637BFDAE47514D4022891B64E6491EC0AD11E246E1FA4840'::geometry ~ real_estate.geom) AND _st_contains('0103000020E6100000010000000500000022891B64E6491EC0AD11E246E1FA4840A267FAD58CE7FA3FAD11E246E1FA4840A267FAD58CE7FA3F637BFDAE47514D4022891B64E6491EC0637BFDAE47514D4022891B64E6491EC0AD11E246E1FA4840'::geometry, real_estate.geom))
  • Buffers: shared hit=287449
13. 576.897 576.897 ↑ 1.0 1 66,565 / 3

Index Only Scan using lender_loan_reference_lender_reference_idx on lender lender (cost=0.43..0.57 rows=1 width=13) (actual time=0.025..0.026 rows=1 loops=66,565)

  • Index Cond: (lender.loan_reference = real_estate_loan.loan_reference)
  • Heap Fetches: 68427
  • Buffers: shared hit=257486
14.          

CTE clusters

15. 35,665.795 37,880.490 ↓ 2,445.4 41,572 1

WindowAgg (cost=0..0.55 rows=17 width=68) (actual time=37,870.526..37,880.49 rows=41,572 loops=1)

  • Buffers: shared hit=550173, temp read=507 written=814
16. 2,214.695 2,214.695 ↓ 2,445.4 41,572 1

CTE Scan on ll ll (cost=0..0.34 rows=17 width=64) (actual time=2,100.455..2,214.695 rows=41,572 loops=1)

  • Buffers: shared hit=550173, temp read=507 written=814
17.          

CTE cluster_centres

18. 179.041 38,072.811 ↑ 17.0 1 1

HashAggregate (cost=0.51..0.81 rows=17 width=76) (actual time=38,072.81..38,072.811 rows=1 loops=1)

  • Group Key: clusters.cluster
  • Buffers: shared hit=550173, temp read=507 written=814
19. 37,893.770 37,893.770 ↓ 2,445.4 41,572 1

CTE Scan on clusters clusters (cost=0..0.34 rows=17 width=68) (actual time=37,870.533..37,893.77 rows=41,572 loops=1)

  • Filter: (clusters.cluster IS NOT NULL)
  • Buffers: shared hit=550173, temp read=507 written=814
Planning time : 1.935 ms
Execution time : 38,080.081 ms