explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8e9vC

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 93.996 ↓ 0.0 0 1

Unique (cost=4,512.51..4,512.59 rows=1 width=402) (actual time=93.996..93.996 rows=0 loops=1)

2. 0.030 93.996 ↓ 0.0 0 1

Sort (cost=4,512.51..4,512.51 rows=1 width=402) (actual time=93.996..93.996 rows=0 loops=1)

  • Sort Key: restaurants.id, restaurants.name, restaurants.latitude, restaurants.longitude, restaurants.created_at, restaurants.updated_at, restaurants.open_at, restaurants.close_at, restaurants.address, restaurants.cover, restaurants.logo, restaurants.phone_number, restaurants.out_app_id, restaurants.deleted, restaurants.account_id, restaurants.is_using_system, restaurants.is_paying, restaurants.is_online, restaurants.be_online_at, restaurants.is_premium, restaurants.in_home, restaurants.is_exclusive, restaurants.james_subtotal_percentage, restaurants.deleted_at, restaurants.gpa_id, restaurants.free_delivery_start_at, restaurants.free_delivery_end_at, restaurants.promo_cover, restaurants.is_retail, restaurants.retail_fee, restaurants.free_tmo_ends_at, restaurants.tmo_price
  • Sort Method: quicksort Memory: 25kB
3. 0.001 93.966 ↓ 0.0 0 1

Nested Loop Left Join (cost=54.19..4,512.50 rows=1 width=402) (actual time=93.966..93.966 rows=0 loops=1)

  • Join Filter: (business_hours.restaurant_id = restaurants.id)
4. 76.915 93.965 ↓ 0.0 0 1

Nested Loop Left Join (cost=54.19..4,458.36 rows=1 width=402) (actual time=93.965..93.965 rows=0 loops=1)

  • Filter: ((btrim((restaurants.name)::text) ~~* '%cone pequeno%'::text) OR ((tags.tag_name)::text ~~* '%cone pequeno%'::text) OR ((set_menus.title)::text ~~* '%cone pequeno%'::text))
  • Rows Removed by Filter: 11584
5. 0.195 10.327 ↓ 81.0 81 1

Nested Loop Left Join (cost=30.14..1,318.94 rows=1 width=410) (actual time=3.463..10.327 rows=81 loops=1)

6. 2.012 9.889 ↓ 81.0 81 1

Nested Loop Left Join (cost=30.00..1,318.74 rows=1 width=406) (actual time=3.447..9.889 rows=81 loops=1)

  • Join Filter: (restaurants_has_tags.restaurant_id = restaurants.id)
  • Rows Removed by Join Filter: 10385
7. 0.200 6.352 ↓ 25.0 25 1

Bitmap Heap Scan on restaurants (cost=30.00..1,305.34 rows=1 width=402) (actual time=3.385..6.352 rows=25 loops=1)

  • Recheck Cond: ((out_app_id IS NULL) AND (city_id = 1))
  • Filter: ((NOT deleted) AND ((SubPlan 1) = 0))
  • Rows Removed by Filter: 36
  • Heap Blocks: exact=8
8. 0.009 1.699 ↓ 0.0 0 1

BitmapAnd (cost=30.00..30.00 rows=89 width=0) (actual time=1.699..1.699 rows=0 loops=1)

9. 0.026 0.026 ↑ 1.0 114 1

Bitmap Index Scan on index_restaurants_on_out_app_id (cost=0.00..9.13 rows=114 width=0) (actual time=0.025..0.026 rows=114 loops=1)

  • Index Cond: (out_app_id IS NULL)
10. 1.624 1.624 ↑ 1.0 288 1

Bitmap Index Scan on trgm_idx_restaurants_multi (cost=0.00..10.16 rows=288 width=0) (actual time=1.624..1.624 rows=288 loops=1)

  • Index Cond: (city_id = 1)
11. 0.040 0.040 ↑ 1.0 275 1

Bitmap Index Scan on index_restaurants_on_deleted (cost=0.00..10.21 rows=275 width=0) (actual time=0.040..0.040 rows=275 loops=1)

  • Index Cond: (deleted = false)
12.          

SubPlan (forBitmap Heap Scan)

13. 0.366 4.453 ↑ 1.0 1 61

Aggregate (cost=14.20..14.21 rows=1 width=8) (actual time=0.073..0.073 rows=1 loops=61)

14. 0.305 4.087 ↑ 1.0 1 61

Bitmap Heap Scan on business_hours business_hours_1 (cost=8.58..14.20 rows=1 width=0) (actual time=0.067..0.067 rows=1 loops=61)

  • Recheck Cond: (((restaurant_id = restaurants.id) AND (day_of_week = 2) AND ((open_at)::text <= '19:16'::text)) OR ((restaurant_id = restaurants.id) AND (day_of_week = 1) AND ((close_at)::text >= '19:16'::text)))
  • Filter: (((restaurants.is_online IS TRUE) AND (day_of_week = 2) AND ((open_at)::text <= '19:16'::text) AND (((close_at)::text = '00:00'::text) OR ((close_at)::text >= '19:16'::text) OR ((close_at)::text < (open_at)::text))) OR ((day_of_week = 1) AND ((close_at)::text <> '00:00'::text) AND ((close_at)::text < (open_at)::text) AND ((close_at)::text >= '19:16'::text)))
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=43
15. 0.183 3.782 ↓ 0.0 0 61

BitmapOr (cost=8.58..8.58 rows=2 width=0) (actual time=0.062..0.062 rows=0 loops=61)

16. 3.355 3.355 ↑ 1.0 1 61

Bitmap Index Scan on business_hours_multicolumn_idx (cost=0.00..4.29 rows=1 width=0) (actual time=0.055..0.055 rows=1 loops=61)

  • Index Cond: ((restaurant_id = restaurants.id) AND (day_of_week = 2) AND ((open_at)::text <= '19:16'::text))
17. 0.244 0.244 ↓ 0.0 0 61

Bitmap Index Scan on business_hours_multicolumn_idx (cost=0.00..4.29 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=61)

  • Index Cond: ((restaurant_id = restaurants.id) AND (day_of_week = 1) AND ((close_at)::text >= '19:16'::text))
18. 1.525 1.525 ↑ 1.0 418 25

Seq Scan on restaurants_has_tags (cost=0.00..8.18 rows=418 width=8) (actual time=0.006..0.061 rows=418 loops=25)

19. 0.243 0.243 ↑ 1.0 1 81

Index Scan using tags_pkey on tags (cost=0.14..0.20 rows=1 width=12) (actual time=0.003..0.003 rows=1 loops=81)

  • Index Cond: (restaurants_has_tags.tag_id = id)
20. 3.564 6.723 ↑ 10.9 143 81

Bitmap Heap Scan on set_menus (cost=24.05..3,108.31 rows=1,555 width=55) (actual time=0.043..0.083 rows=143 loops=81)

  • Recheck Cond: (restaurant_id = restaurants.id)
  • Heap Blocks: exact=689
21. 3.159 3.159 ↑ 10.9 143 81

Bitmap Index Scan on trgm_idx_set_menus_multi_column (cost=0.00..23.66 rows=1,555 width=0) (actual time=0.039..0.039 rows=143 loops=81)

  • Index Cond: (restaurant_id = restaurants.id)
22. 0.000 0.000 ↓ 0.0 0

Seq Scan on business_hours (cost=0.00..30.73 rows=1,873 width=4) (never executed)