explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3A8o

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 5.579 277,013.489 ↓ 2,317.0 2,317 1

Nested Loop (cost=5.76..48.08 rows=1 width=229) (actual time=0.168..277,013.489 rows=2,317 loops=1)

2. 443.192 276,989.374 ↓ 2,317.0 2,317 1

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

  • Join Filter: ("Positions"."positionId" = "Positions_1"."positionId")
  • Rows Removed by Join Filter: 4269997
3. 6.672 6.672 ↓ 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..6.672 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,140.410 276,539.510 ↓ 1,933.0 1,933 2,210

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

5. 3,449.810 269,127.170 ↓ 1,933.0 1,933 2,210

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

6. 1,935.960 1,935.960 ↓ 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.876 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. 239,320.900 263,741.400 ↑ 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.054 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. 18.536 18.536 ↑ 1.0 1 2,317

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

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