explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qsyY : Optimization for: plan #HUQO

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 150.597 2,912.203 ↓ 100.0 100 1

Limit (cost=486,145.31..656,757.34 rows=1 width=65) (actual time=2,742.840..2,912.203 rows=100 loops=1)

2. 5.019 2,761.606 ↓ 100.0 100 1

Nested Loop (cost=486,145.31..656,757.34 rows=1 width=65) (actual time=2,742.839..2,761.606 rows=100 loops=1)

  • Join Filter: (pp."PlaceID" = p2."ID")
  • Rows Removed by Join Filter: 74,167
3. 0.000 2,743.187 ↓ 100.0 100 1

Nested Loop (cost=486,145.31..655,541.24 rows=1 width=87) (actual time=2,742.355..2,743.187 rows=100 loops=1)

4. 123.694 2,892.863 ↓ 49.0 49 1

Gather (cost=486,144.76..655,539.02 rows=1 width=87) (actual time=2,742.279..2,892.863 rows=49 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 90.650 2,769.169 ↓ 191.0 191 3 / 3

Merge Left Join (cost=485,144.76..654,538.92 rows=1 width=87) (actual time=2,698.359..2,769.169 rows=191 loops=3)

  • Merge Cond: (((p."StreetType")::text = (s."StreetType")::text) AND ((lower((p."Name")::text)) = (lower((s."Name")::text))) AND ((p."DirectionPrefix")::text = (s."DirectionPrefix")::text) AND ((p."DirectionSuffix")::text = (s."DirectionSuffix")::text))
  • Join Filter: ((s."FromAddress" <= p."FromAddress") AND (p."FromAddress" <= s."ToAddress") AND (s."CountyID" = p."CountyID"))
  • Rows Removed by Join Filter: 272,792
  • Filter: (s."ID" IS NULL)
  • Rows Removed by Filter: 2,820
6. 622.608 1,929.724 ↑ 306.4 535 3 / 3

Sort (cost=398,818.96..399,228.75 rows=163,916 width=69) (actual time=1,929.242..1,929.724 rows=535 loops=3)

  • Sort Key: p."StreetType", (lower((p."Name")::text)), p."DirectionPrefix", p."DirectionSuffix
  • Sort Method: external merge Disk: 15,824kB
  • Worker 0: Sort Method: external merge Disk: 13,656kB
  • Worker 1: Sort Method: external merge Disk: 14,992kB
7. 1,307.116 1,307.116 ↓ 1.0 165,755 3 / 3

Parallel Seq Scan on "Parcels" p (cost=0.00..377,898.21 rows=163,916 width=69) (actual time=1.970..1,307.116 rows=165,755 loops=3)

  • Filter: ("CountyID" = 'c1ab223c-eb20-11ea-b74f-00155d2b2709'::uuid)
  • Rows Removed by Filter: 1,125,024
8. 16.465 748.795 ↓ 1.7 361,325 3 / 3

Materialize (cost=86,325.79..87,375.68 rows=209,977 width=50) (actual time=676.020..748.795 rows=361,325 loops=3)

9. 510.836 732.330 ↑ 2.4 86,282 3 / 3

Sort (cost=86,325.79..86,850.74 rows=209,977 width=50) (actual time=676.015..732.330 rows=86,282 loops=3)

  • Sort Key: s."StreetType", (lower((s."Name")::text)), s."DirectionPrefix", s."DirectionSuffix
  • Sort Method: external merge Disk: 13,072kB
  • Worker 0: Sort Method: external merge Disk: 13,072kB
  • Worker 1: Sort Method: external merge Disk: 13,072kB
10. 204.802 221.494 ↑ 1.0 206,316 3 / 3

Bitmap Heap Scan on "Streets" s (cost=5,795.75..60,585.46 rows=209,977 width=50) (actual time=18.987..221.494 rows=206,316 loops=3)

  • Recheck Cond: ("CountyID" = 'c1ab223c-eb20-11ea-b74f-00155d2b2709'::uuid)
  • Heap Blocks: exact=15,240
11. 16.692 16.692 ↑ 1.0 206,316 3 / 3

Bitmap Index Scan on "IX_Streets_CountyID_ToNode" (cost=0.00..5,743.25 rows=209,977 width=0) (actual time=16.692..16.692 rows=206,316 loops=3)

  • Index Cond: ("CountyID" = 'c1ab223c-eb20-11ea-b74f-00155d2b2709'::uuid)
12. 0.833 0.833 ↑ 1.0 2 49

Index Only Scan using "PK_ParcelPlaces" on "ParcelPlaces" pp (cost=0.56..2.20 rows=2 width=32) (actual time=0.013..0.017 rows=2 loops=49)

  • Index Cond: ("ParcelID" = p."ID")
  • Heap Fetches: 100
13. 13.400 13.400 ↑ 4.7 743 100

Seq Scan on "Places" p2 (cost=0.00..1,172.71 rows=3,471 width=26) (actual time=0.001..0.134 rows=743 loops=100)

Planning time : 1.519 ms
Execution time : 2,917.841 ms