<aside> 💡
요약
쿼리 (psy6sy를 사용해서 hibernate가 자동으로 만들어낸 쿼리를 정확히 로그에서 확인할 수 있다)
EXPLAIN ANALYZE select distinct he1_0.hotel_id,he1_0.address,he1_0.description,he1_0.latitude,he1_0.longitude,he1_0.name,he1_0.provider_id,he1_0.rating,he1_0.review_count,he1_0.star_level from hotel_entity he1_0 join room_entity re1_0 on re1_0.hotel_id=he1_0.hotel_id left join reservation_entity re2_0 on re2_0.hotel_id=he1_0.hotel_id left join reserved_room_entity rre1_0 on rre1_0.reservation_id=re2_0.reservation_id where he1_0.latitude between 37.45533155454545 and 37.546240645454546 and he1_0.longitude between 126.9795912961723 and 127.0941809038277 and re1_0.total_quantity>(select coalesce(sum(rre2_0.quantity),0) from reserved_room_entity rre2_0 join reservation_entity r2_0 on r2_0.reservation_id=rre2_0.reservation_id where rre2_0.room_id=re1_0.room_id and r2_0.check_in_date<'2025-01-11 00:00:00' and r2_0.check_out_date>'2025-01-10 00:00:00') and (st_distance_sphere(point(he1_0.longitude,he1_0.latitude),point(127.0368861,37.5007861))/1000.0)<=5.0 order by (st_distance_sphere(point(he1_0.longitude,he1_0.latitude),point(127.0368861,37.5007861))/1000.0) limit 0,20
EXPLAIN ANALYZE select count(distinct he1_0.hotel_id) from hotel_entity he1_0 join room_entity re1_0 on re1_0.hotel_id=he1_0.hotel_id left join reservation_entity re2_0 on re2_0.hotel_id=he1_0.hotel_id left join reserved_room_entity rre1_0 on rre1_0.reservation_id=re2_0.reservation_id where he1_0.latitude between 37.45533155454545 and 37.546240645454546 and he1_0.longitude between 126.9795912961723 and 127.0941809038277 and re1_0.total_quantity>(select coalesce(sum(rre2_0.quantity),0) from reserved_room_entity rre2_0 join reservation_entity r2_0 on r2_0.reservation_id=rre2_0.reservation_id where rre2_0.room_id=re1_0.room_id and r2_0.check_in_date<'2025-01-11 00:00:00' and r2_0.check_out_date>'2025-01-10 00:00:00') and (st_distance_sphere(point(he1_0.longitude,he1_0.latitude),point(127.0368861,37.5007861))/1000.0)<=5.0
EXPLAIN ANALYZE select p1_0.hotel_id,p1_0.photo_id,p1_0.created_at,p1_0.display_type,p1_0.room_id,p1_0.stored_file_name,p1_0.upload_file_name from photo_entity p1_0 where p1_0.hotel_id in (6319,5418,2397,8341,7284,561,9085,8080,4827,5294,6693,5964,7454,8812,9274,2186,7745,9454,42,4046)
FULL TABLE SCAN을 한다. rows는 1만개, Filtered의 값을 보면 25다. 값이 낮을 수록 필요없는 데이터를 가져온다는 뜻인데, 지금 필요없는 75%의 데이터를 가져오고있다.
-> Limit: 20 row(s) (actual time=61.1..61.1 rows=20 loops=1)
-> Sort: `(st_distance_sphere(point(he1_0.longitude,he1_0.latitude),point(127.0368861,37.5007861)) / 1000.0)`, limit input to 20 row(s) per chunk (actual time=61..61 rows=20 loops=1)
-> Table scan on <temporary> (cost=6198..6232 rows=2547) (actual time=60.5..60.8 rows=1935 loops=1)
-> Temporary table with deduplication (cost=6198..6198 rows=2547) (actual time=60.5..60.5 rows=1935 loops=1)
-> Nested loop left join (cost=5611 rows=2547) (actual time=3.49..52 rows=1935 loops=1)
-> Nested loop left join (cost=2809 rows=2547) (actual time=3.43..51.4 rows=1935 loops=1)
-> Nested loop inner join (cost=1918 rows=2547) (actual time=3.3..49.7 rows=1935 loops=1)
-> Filter: ((he1_0.latitude between 37.45533155454545 and 37.546240645454546) and (he1_0.longitude between 126.9795912961723 and 127.0941809038277) and ((st_distance_sphere(point(he1_0.longitude,he1_0.latitude),<cache>(point(127.0368861,37.5007861))) / 1000.0) <= 5.0)) (cost=1026 rows=2547) (actual time=2.49..31.3 rows=1935 loops=1)
-> Table scan on he1_0 (cost=1026 rows=10022) (actual time=0.608..14.1 rows=10000 loops=1)
-> Filter: (re1_0.total_quantity > (select #2)) (cost=0.25 rows=1) (actual time=0.00862..0.00927 rows=1 loops=1935)
-> Index lookup on re1_0 using FKt0w4t2wslr29ebewyxtnpqkxj (hotel_id = he1_0.hotel_id) (cost=0.25 rows=1) (actual time=0.00584..0.00644 rows=1 loops=1935)
-> Select #2 (subquery in condition; dependent)
-> Aggregate: sum(rre2_0.quantity) (cost=2.43 rows=1) (actual time=0.00155..0.00159 rows=1 loops=1935)
-> Nested loop inner join (cost=2.2 rows=1) (actual time=0.00128..0.00128 rows=0 loops=1935)
-> Index lookup on rre2_0 using FKmqa3r6i177v4i5wmijwl4ngx4 (room_id = re1_0.room_id) (cost=1.1 rows=1) (actual time=0.00115..0.00115 rows=0 loops=1935)
-> Filter: ((r2_0.check_in_date < DATE'2025-01-11') and (r2_0.check_out_date > DATE'2025-01-10')) (cost=1.1 rows=1) (never executed)
-> Single-row index lookup on r2_0 using PRIMARY (reservation_id = rre2_0.reservation_id) (cost=1.1 rows=1) (never executed)
-> Covering index lookup on re2_0 using FKkgw1pt1ikytl1kee9c2fbmpuj (hotel_id = he1_0.hotel_id) (cost=0.25 rows=1) (actual time=742e-6..742e-6 rows=0 loops=1935)
-> Covering index lookup on rre1_0 using FKm41wjpd7xi0sxu8xpmyn66qap (reservation_id = re2_0.reservation_id) (cost=1 rows=1) (actual time=76.8e-6..76.8e-6 rows=0 loops=1935)