explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7rqC : Optimization for: plan #3A8o

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 5.296 276,332.930 ↓ 2,317.0 2,317 1

Nested Loop (cost=5.76..48.08 rows=1 width=229) (actual time=0.123..276,332.930 rows=2,317 loops=1)

2. 464.398 276,306.781 ↓ 2,317.0 2,317 1

Nested Loop Left Join (cost=5.49..39.78 rows=1 width=213) (actual time=0.114..276,306.781 rows=2,317 loops=1)

  • Join Filter: ("Positions"."positionId" = "Positions_1"."positionId")
  • Rows Removed by Join Filter: 4269997
3. 7.863 7.863 ↓ 2,210.0 2,210 1

Index Scan using "spaceId_index" on "Positions" (cost=0.43..8.45 rows=1 width=96) (actual time=0.022..7.863 rows=2,210 loops=1)

  • Index Cond: ("spaceId" = 'a252f517-26ce-4068-a889-0675391146b0'::uuid)
  • Filter: ((t >= '2019-07-30 22:00:00'::timestamp without time zone) AND (t <= '2019-08-05 22:00:00'::timestamp without time zone))
4. 3,067.480 275,834.520 ↓ 1,933.0 1,933 2,210

Nested Loop (cost=5.06..31.32 rows=1 width=125) (actual time=0.052..124.812 rows=1,933 loops=2,210)

5. 7,712.900 268,495.110 ↓ 1,933.0 1,933 2,210

Nested Loop (cost=4.79..30.87 rows=1 width=40) (actual time=0.050..121.491 rows=1,933 loops=2,210)

6. 1,924.910 1,924.910 ↓ 2,210.0 2,210 2,210

Index Scan using "spaceId_index" on "Positions" "Positions_1" (cost=0.43..8.45 rows=1 width=56) (actual time=0.018..0.871 rows=2,210 loops=2,210)

  • Index Cond: ("spaceId" = 'a252f517-26ce-4068-a889-0675391146b0'::uuid)
  • Filter: ((t >= '2019-07-30 22:00:00'::timestamp without time zone) AND (t <= '2019-08-05 22:00:00'::timestamp without time zone))
7. 234,436.800 258,857.300 ↑ 1.0 1 4,884,100

Bitmap Heap Scan on "RegionsToMaps" (cost=4.36..22.41 rows=1 width=134) (actual time=0.035..0.053 rows=1 loops=4,884,100)

  • Recheck Cond: ("mapId" = "Positions_1"."mapId")
  • Filter: (((("regionType")::text = 'circular'::text) OR (("regionType")::text = 'rectangular'::text)) AND (((("regionType")::text = 'circular'::text) AND ("Positions_1".xy <@ circle(point((center[1])::double precision, (center[2])::double precision), (radius)::double precision))) OR ((("regionType")::text = 'rectangular'::text) AND ("Positions_1".xy <@ box(point(("bottomLeft"[1])::double precision, ("bottomLeft"[2])::double precision), point(("topRight"[1])::double precision, ("topRight"[2])::double precision))))))
  • Rows Removed by Filter: 59
  • Heap Blocks: exact=63493300
8. 24,420.500 24,420.500 ↓ 5.5 60 4,884,100

Bitmap Index Scan on "regionsToMaps_mapId_index" (cost=0.00..4.36 rows=11 width=0) (actual time=0.005..0.005 rows=60 loops=4,884,100)

  • Index Cond: ("mapId" = "Positions_1"."mapId")
9. 4,271.930 4,271.930 ↑ 1.0 1 4,271,930

Index Scan using "Regions_pkey" on "Regions" (cost=0.28..0.44 rows=1 width=133) (actual time=0.001..0.001 rows=1 loops=4,271,930)

  • Index Cond: ("regionId" = "RegionsToMaps"."regionId")
  • Filter: ("spaceId" = 'a252f517-26ce-4068-a889-0675391146b0'::uuid)
10. 20.853 20.853 ↑ 1.0 1 2,317

Index Scan using "Maps_pkey" on "Maps" (cost=0.27..8.29 rows=1 width=32) (actual time=0.009..0.009 rows=1 loops=2,317)

  • Index Cond: ("mapId" = "Positions"."mapId")
Planning time : 1.029 ms