explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4Qfh

Settings
# exclusive inclusive rows x rows loops node
1. 0.002 1,536.994 ↑ 6.0 1 1

Unique (cost=5,608.98..5,609.48 rows=6 width=402) (actual time=1,536.993..1,536.994 rows=1 loops=1)

2. 3.255 1,536.992 ↑ 6.0 1 1

Sort (cost=5,608.98..5,609.00 rows=6 width=402) (actual time=1,536.992..1,536.992 rows=1 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.009 1,533.737 ↑ 6.0 1 1

Nested Loop (cost=30.17..5,608.90 rows=6 width=402) (actual time=1,349.054..1,533.737 rows=1 loops=1)

4. 1,199.915 1,533.645 ↑ 8.0 1 1

Hash Left Join (cost=29.89..5,595.91 rows=8 width=402) (actual time=1,348.964..1,533.645 rows=1 loops=1)

  • Hash Cond: (restaurants_has_tags.tag_id = tags.id)
  • 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: 249351
5. 285.620 333.591 ↓ 9.5 249,352 1

Hash Right Join (cost=24.85..5,517.89 rows=26,241 width=457) (actual time=2.401..333.591 rows=249,352 loops=1)

  • Hash Cond: (set_menus.restaurant_id = restaurants.id)
6. 45.658 45.658 ↑ 1.0 71,545 1

Seq Scan on set_menus (cost=0.00..4,962.45 rows=71,545 width=55) (actual time=0.012..45.658 rows=71,545 loops=1)

7. 0.545 2.313 ↓ 2.6 302 1

Hash (cost=23.39..23.39 rows=117 width=406) (actual time=2.313..2.313 rows=302 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 65kB
8. 1.383 1.768 ↓ 2.6 302 1

Hash Right Join (cost=14.10..23.39 rows=117 width=406) (actual time=1.265..1.768 rows=302 loops=1)

  • Hash Cond: (restaurants_has_tags.restaurant_id = restaurants.id)
9. 0.101 0.101 ↑ 1.0 418 1

Seq Scan on restaurants_has_tags (cost=0.00..8.18 rows=418 width=8) (actual time=0.014..0.101 rows=418 loops=1)

10. 0.062 0.284 ↑ 1.5 61 1

Hash (cost=12.99..12.99 rows=89 width=402) (actual time=0.284..0.284 rows=61 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 19kB
11. 0.222 0.222 ↑ 1.5 61 1

Seq Scan on restaurants (cost=0.00..12.99 rows=89 width=402) (actual time=0.042..0.222 rows=61 loops=1)

  • Filter: ((out_app_id IS NULL) AND (NOT deleted) AND (city_id = 1))
  • Rows Removed by Filter: 258
12. 0.081 0.139 ↑ 1.0 135 1

Hash (cost=3.35..3.35 rows=135 width=12) (actual time=0.139..0.139 rows=135 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
13. 0.058 0.058 ↑ 1.0 135 1

Seq Scan on tags (cost=0.00..3.35 rows=135 width=12) (actual time=0.019..0.058 rows=135 loops=1)

14. 0.083 0.083 ↑ 1.0 1 1

Index Scan using index_business_hours_on_restaurant_id on business_hours (cost=0.28..1.61 rows=1 width=20) (actual time=0.082..0.083 rows=1 loops=1)

  • Index Cond: (restaurant_id = restaurants.id)
  • Filter: ((((day_of_week = 2) AND ((open_at)::text <= '18:59'::text) AND (((close_at)::text = '00:00'::text) OR ((close_at)::text >= '18:59'::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 >= '18:59'::text))) AND (((restaurants.is_online IS TRUE) AND (day_of_week = 2) AND ((open_at)::text <= '18:59'::text) AND (((close_at)::text = '00:00'::text) OR ((close_at)::text >= '18:59'::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 >= '18:59'::text))))