explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6XqJ

Settings
# exclusive inclusive rows x rows loops node
1. 18.101 108.796 ↓ 157.0 157 1

Nested Loop (cost=457.26..798.34 rows=1 width=69) (actual time=49.683..108.796 rows=157 loops=1)

  • Join Filter: (min_areas.min_area = delivery_areas_geometry.area)
  • Rows Removed by Join Filter: 19
2. 3.979 74.942 ↓ 267.0 267 1

Hash Join (cost=456.84..794.73 rows=1 width=77) (actual time=48.925..74.942 rows=267 loops=1)

  • Hash Cond: (delivery_areas.restaurant_id = min_areas.rid)
3. 8.147 23.075 ↓ 6.6 231 1

Nested Loop (cost=9.52..347.27 rows=35 width=69) (actual time=0.891..23.075 rows=231 loops=1)

4. 6.315 10.044 ↓ 9.9 407 1

Nested Loop (cost=9.10..317.59 rows=41 width=25) (actual time=0.675..10.044 rows=407 loops=1)

5. 0.051 0.081 ↑ 1.0 2 1

Bitmap Heap Scan on postal_codes (cost=4.30..11.10 rows=2 width=21) (actual time=0.054..0.081 rows=2 loops=1)

  • Recheck Cond: (code = '1223'::bpchar)
  • Heap Blocks: exact=2
6. 0.030 0.030 ↑ 1.0 2 1

Bitmap Index Scan on idx_502872_postal_codes_code_index (cost=0.00..4.30 rows=2 width=0) (actual time=0.023..0.030 rows=2 loops=1)

  • Index Cond: (code = '1223'::bpchar)
7. 3.582 3.648 ↓ 4.2 204 2

Bitmap Heap Scan on delivery_areas_postal_codes (cost=4.80..152.75 rows=49 width=12) (actual time=0.122..1.824 rows=204 loops=2)

  • Recheck Cond: (postal_code_id = postal_codes.id)
  • Heap Blocks: exact=201
8. 0.066 0.066 ↓ 4.2 204 2

Bitmap Index Scan on idx_502857_delivery_areas_postal_codes_postal_code_id_foreign (cost=0.00..4.79 rows=49 width=0) (actual time=0.030..0.033 rows=204 loops=2)

  • Index Cond: (postal_code_id = postal_codes.id)
9. 4.884 4.884 ↑ 1.0 1 407

Index Scan using idx_502849_primary on delivery_areas (cost=0.42..0.72 rows=1 width=44) (actual time=0.012..0.012 rows=1 loops=407)

  • Index Cond: (id = delivery_areas_postal_codes.delivery_area_id)
  • Filter: is_active
  • Rows Removed by Filter: 0
10. 1.216 47.888 ↓ 154.0 154 1

Hash (cost=447.31..447.31 rows=1 width=16) (actual time=47.881..47.888 rows=154 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
11. 2.301 46.672 ↓ 154.0 154 1

Subquery Scan on min_areas (cost=447.28..447.31 rows=1 width=16) (actual time=40.772..46.672 rows=154 loops=1)

12. 2.480 44.371 ↓ 154.0 154 1

GroupAggregate (cost=447.28..447.30 rows=1 width=20) (actual time=40.756..44.371 rows=154 loops=1)

  • Group Key: delivery_areas_1.type, delivery_areas_1.restaurant_id
13. 2.579 41.891 ↓ 160.0 160 1

Sort (cost=447.28..447.28 rows=1 width=20) (actual time=40.718..41.891 rows=160 loops=1)

  • Sort Key: delivery_areas_1.type, delivery_areas_1.restaurant_id
  • Sort Method: quicksort Memory: 37kB
14. 6.499 39.312 ↓ 160.0 160 1

Nested Loop (cost=9.94..447.27 rows=1 width=20) (actual time=0.722..39.312 rows=160 loops=1)

15. 8.705 25.421 ↓ 6.6 231 1

Nested Loop (cost=9.52..347.27 rows=35 width=28) (actual time=0.550..25.421 rows=231 loops=1)

16. 6.694 11.425 ↓ 9.9 407 1

Nested Loop (cost=9.10..317.59 rows=41 width=8) (actual time=0.443..11.425 rows=407 loops=1)

17. 0.224 0.261 ↑ 1.0 2 1

Bitmap Heap Scan on postal_codes postal_codes_1 (cost=4.30..11.10 rows=2 width=4) (actual time=0.051..0.261 rows=2 loops=1)

  • Recheck Cond: (code = '1223'::bpchar)
  • Heap Blocks: exact=2
18. 0.037 0.037 ↑ 1.0 2 1

Bitmap Index Scan on idx_502872_postal_codes_code_index (cost=0.00..4.30 rows=2 width=0) (actual time=0.030..0.037 rows=2 loops=1)

  • Index Cond: (code = '1223'::bpchar)
19. 4.406 4.470 ↓ 4.2 204 2

Bitmap Heap Scan on delivery_areas_postal_codes delivery_areas_postal_codes_1 (cost=4.80..152.75 rows=49 width=12) (actual time=0.110..2.235 rows=204 loops=2)

  • Recheck Cond: (postal_code_id = postal_codes_1.id)
  • Heap Blocks: exact=201
20. 0.064 0.064 ↓ 4.2 204 2

Bitmap Index Scan on idx_502857_delivery_areas_postal_codes_postal_code_id_foreign (cost=0.00..4.79 rows=49 width=0) (actual time=0.028..0.032 rows=204 loops=2)

  • Index Cond: (postal_code_id = postal_codes_1.id)
21. 5.291 5.291 ↑ 1.0 1 407

Index Scan using idx_502849_primary on delivery_areas delivery_areas_1 (cost=0.42..0.72 rows=1 width=20) (actual time=0.013..0.013 rows=1 loops=407)

  • Index Cond: (id = delivery_areas_postal_codes_1.delivery_area_id)
  • Filter: is_active
  • Rows Removed by Filter: 0
22. 7.392 7.392 ↑ 1.0 1 231

Index Scan using idx_507617_delivery_areas_geometry_delivery_area_id_foreign on delivery_areas_geometry delivery_areas_geometry_1 (cost=0.42..2.85 rows=1 width=16) (actual time=0.026..0.032 rows=1 loops=231)

  • Index Cond: (delivery_area_id = delivery_areas_1.id)
  • Filter: ((shape ~ '0101000020E610000000000020A4B9184000000054A51C4740'::geometry) AND _st_contains(shape, '0101000020E610000000000020A4B9184000000054A51C4740'::geometry))
  • Rows Removed by Filter: 0
23. 15.753 15.753 ↑ 1.0 1 267

Index Scan using idx_507617_delivery_areas_geometry_delivery_area_id_foreign on delivery_areas_geometry (cost=0.42..2.85 rows=1 width=48) (actual time=0.054..0.059 rows=1 loops=267)

  • Index Cond: (delivery_area_id = delivery_areas.id)
  • Filter: ((shape ~ '0101000020E610000000000020A4B9184000000054A51C4740'::geometry) AND _st_contains(shape, '0101000020E610000000000020A4B9184000000054A51C4740'::geometry))" Rows Removed by Filter: 0