explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ad9x

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

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

2. 0.033 1,520.569 ↑ 6.0 1 1

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

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

4. 1,202.423 1,520.456 ↑ 8.0 1 1

Hash Left Join (cost=29.89..5,595.91 rows=8 width=402) (actual time=1,337.023..1,520.456 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. 281.537 317.913 ↓ 9.5 249,352 1

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

  • Hash Cond: (set_menus.restaurant_id = restaurants.id)
6. 35.164 35.164 ↑ 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..35.164 rows=71,545 loops=1)

7. 0.475 1.212 ↓ 2.6 302 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 65kB
8. 0.461 0.737 ↓ 2.6 302 1

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

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

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

10. 0.021 0.186 ↑ 1.5 61 1

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

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

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

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

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

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

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

14. 0.072 0.072 ↑ 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.071..0.072 rows=1 loops=1)

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