bgpat
commented
4 years ago 
isucon@team162-001:~/isuumo/webapp/go$ head -20 slow.log
Count: 399 Time=0.11s (44s) Lock=0.00s (0s) Rows=25.0 (9975), isucon[isucon]@localhost
SELECT id, thumbnail, name, description, latitude, longitude, address, rent, door_height, door_width, features, popularity FROM estate WHERE rent >= N AND rent < N ORDER BY popularity DESC, id ASC LIMIT N OFFSET N
Count: 39 Time=0.56s (21s) Lock=0.00s (0s) Rows=25.0 (975), isucon[isucon]@localhost
SELECT id, name, description, thumbnail, price, height, width, depth, color, features, kind, popularity, stock FROM chair WHERE height >= N AND height < N AND stock_flag = TRUE ORDER BY popularity DESC, id ASC LIMIT N OFFSET N
Count: 180 Time=0.10s (18s) Lock=0.00s (0s) Rows=25.0 (4500), isucon[isucon]@localhost
SELECT id, thumbnail, name, description, latitude, longitude, address, rent, door_height, door_width, features, popularity FROM estate WHERE rent >= N ORDER BY popularity DESC, id ASC LIMIT N OFFSET N
Count: 33 Time=0.54s (17s) Lock=0.00s (0s) Rows=25.0 (825), isucon[isucon]@localhost
SELECT id, name, description, thumbnail, price, height, width, depth, color, features, kind, popularity, stock FROM chair WHERE depth >= N AND depth < N AND stock_flag = TRUE ORDER BY popularity DESC, id ASC LIMIT N OFFSET N
Count: 28 Time=0.57s (15s) Lock=0.00s (0s) Rows=25.0 (700), isucon[isucon]@localhost
SELECT id, name, description, thumbnail, price, height, width, depth, color, features, kind, popularity, stock FROM chair WHERE height < N AND stock_flag = TRUE ORDER BY popularity DESC, id ASC LIMIT N OFFSET N
Count: 123 Time=0.12s (15s) Lock=0.00s (0s) Rows=20.0 (2460), isucon[isucon]@localhost
SELECT id, thumbnail, name, description, latitude, longitude, address, rent, door_height, door_width, features, popularity FROM estate WHERE (door_width >= N AND door_height >= N) OR (door_width >= N AND door_height >= N) ORDER BY popularity DESC, id ASC LIMIT N
Count: 141 Time=0.10s (14s) Lock=0.00s (0s) Rows=25.0 (3525), isucon[isucon]@localhost
SELECT id, thumbnail, name, description, latitude, longitude, address, rent, door_height, door_width, features, popularity FROM estate WHERE door_height >= N AND door_height < N ORDER BY popularity DESC, id ASC LIMIT N OFFSET N
price_range_id を追加して price の代わりに利用する