explain.depesz.com

PostgreSQL's explain analyze made readable

Result: TB6A : less slow search

Settings
# exclusive inclusive rows x rows loops node
1. 0.078 13,526.439 ↓ 1,109.0 1,109 1

Limit (cost=4,799.76..4,799.76 rows=1 width=48) (actual time=13,526.313..13,526.439 rows=1,109 loops=1)

2. 0.372 13,526.361 ↓ 1,109.0 1,109 1

Sort (cost=4,799.76..4,799.76 rows=1 width=48) (actual time=13,526.313..13,526.361 rows=1,109 loops=1)

  • Sort Key: (_st_distance(venues.lonlat, '0101000020E6100000000000000088BDBFFED1A4F5F0C04940'::geography, '0'::double precision, true))
  • Sort Method: quicksort Memory: 204kB
3. 2.605 13,525.989 ↓ 1,109.0 1,109 1

Group (cost=4,799.49..4,799.75 rows=1 width=48) (actual time=13,523.510..13,525.989 rows=1,109 loops=1)

  • Group Key: venues.id, spaces.id
4. 1.256 13,523.384 ↓ 1,109.0 1,109 1

Sort (cost=4,799.49..4,799.49 rows=1 width=40) (actual time=13,523.319..13,523.384 rows=1,109 loops=1)

  • Sort Key: venues.id, spaces.id
  • Sort Method: quicksort Memory: 135kB
5. 3,064.454 13,522.128 ↓ 1,109.0 1,109 1

Nested Loop (cost=1,000.73..4,799.48 rows=1 width=40) (actual time=3.345..13,522.128 rows=1,109 loops=1)

  • Join Filter: (spaces.venue_id = venues.id)
  • Rows Removed by Join Filter: 8,564,071
6. 20.164 20.164 ↓ 4,170.0 4,170 1

Index Scan using v_gix on venues (cost=0.15..8.54 rows=1 width=36) (actual time=0.142..20.164 rows=4,170 loops=1)

  • Index Cond: (lonlat && '0101000020E6100000000000000088BDBFFED1A4F5F0C04940'::geography)
  • Filter: (('0101000020E6100000000000000088BDBFFED1A4F5F0C04940'::geography && _st_expand(lonlat, '30000'::double precision)) AND _st_dwithin(lonlat, '0101000020E6100000000000000088BDBFFED1A4F5F0C04940'::geography, '30000'::double precision, true))
  • Rows Removed by Filter: 28
7. 3,786.360 10,437.510 ↓ 1.3 2,054 4,170

Bitmap Heap Scan on spaces (cost=1,000.59..4,770.50 rows=1,635 width=12) (actual time=1.771..2.503 rows=2,054 loops=4,170)

  • Recheck Cond: ((collections && '{22}'::integer[]) AND ((max_state)::text = 'published'::text) AND (deleted_at IS NULL))
  • Heap Blocks: exact=6,867,990
8. 479.550 6,651.150 ↓ 0.0 0 4,170

BitmapAnd (cost=1,000.59..1,000.59 rows=1,635 width=0) (actual time=1.595..1.595 rows=0 loops=4,170)

9. 1,989.090 1,989.090 ↑ 1.0 3,693 4,170

Bitmap Index Scan on idx_spaces_collections (cost=0.00..43.70 rows=3,693 width=0) (actual time=0.477..0.477 rows=3,693 loops=4,170)

  • Index Cond: (collections && '{22}'::integer[])
10. 4,182.510 4,182.510 ↓ 1.1 9,569 4,170

Bitmap Index Scan on idx_spaces_on_max_state_deleted (cost=0.00..955.82 rows=8,741 width=0) (actual time=1.003..1.003 rows=9,569 loops=4,170)

  • Index Cond: (((max_state)::text = 'published'::text) AND (deleted_at IS NULL))
Planning time : 0.310 ms
Execution time : 13,526.560 ms