問題読み会場

リモートワークの普及によって、自宅のイスが生産性に直結することがわかった。その調査結果を受けて、よりよい座環境を求め郊外に住むことを考えるユーザーが増加。また、注目度が増したことで bot からもアクセスが急増し、機会損失が発生している。結果として ISUUMO にアクセスが集中し、負荷に耐えられないことが目立ってきた。

スコアはイスの購入件数と物件の資料請求件数をベースに以下の計算式で計算されます。

スコア = (イスの購入件数 + 物件の資料請求件数) - 減点

bot からのリクエスト
bot からのアクセスはコンバージョンに繋がらないため、弾くことが仕様として決定しましたが、まだ実装されていません。 bot は User-Agent が以下の正規表現にマッチする形式であり、このリクエストに対して 503 Service Unavailable を返すことが許可されています。 これに対するベンチマーカーからの減点は発生しません。

サーバースペック

1CPU
2GB
データ量少ない
- disk 10GB
- 44M /var/lib/mysql/isuumo

nginx

server {
    root /home/isucon/isucon10-qualify/webapp/public;
    listen 80 default_server;
    listen [::]:80 default_server;

    location /api {
            proxy_pass http://localhost:1323;
    }

    location /initialize {
            proxy_pass http://localhost:1323;
    }

    location / {
            root /www/data;
    }
}

画面触る

http://localhost:8080/

フロントエンドはNext.js
- API は素朴な RESTful なやつを叩いている
画像JSなど静的ファイルはnginxから配信されている?
物件検索
- なぞって検索凝っている http://localhost:8080/estate/nazotte/

DB schema

-- MySQL dump 10.13  Distrib 5.7.31, for Linux (x86_64)
--
-- Host: localhost    Database: isuumo
-- ------------------------------------------------------
-- Server version	5.7.31-0ubuntu0.18.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `chair`
--

DROP TABLE IF EXISTS `chair`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `chair` (
  `id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `description` varchar(4096) NOT NULL,
  `thumbnail` varchar(128) NOT NULL,
  `price` int(11) NOT NULL,
  `height` int(11) NOT NULL,
  `width` int(11) NOT NULL,
  `depth` int(11) NOT NULL,
  `color` varchar(64) NOT NULL,
  `features` varchar(64) NOT NULL,
  `kind` varchar(64) NOT NULL,
  `popularity` int(11) NOT NULL,
  `stock` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `estate`
--

DROP TABLE IF EXISTS `estate`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `estate` (
  `id` int(11) NOT NULL,
  `name` varchar(64) NOT NULL,
  `description` varchar(4096) NOT NULL,
  `thumbnail` varchar(128) NOT NULL,
  `address` varchar(128) NOT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `rent` int(11) NOT NULL,
  `door_height` int(11) NOT NULL,
  `door_width` int(11) NOT NULL,
  `features` varchar(64) NOT NULL,
  `popularity` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2020-09-12  3:36:34

物件情報見るとドアのサイズ書いてある。これを使ってユーザーは椅子を探しそう

椅子のページに出るこれ

isucon10q/isuumo/webapp/go/main.go

Lines 817 to 851 in 5b26681

    
           func searchRecommendedEstateWithChair(c echo.Context) error { 
        
           	id, err := strconv.Atoi(c.Param("id")) 
        
           	if err != nil { 
        
           		c.Logger().Infof("Invalid format searchRecommendedEstateWithChair id : %v", err) 
        
           		return c.NoContent(http.StatusBadRequest) 
        
           	} 
        
           	chair := Chair{} 
        
           	query := `SELECT * FROM chair WHERE id = ?` 
        
           	err = db.Get(&chair, query, id) 
        
           	if err != nil { 
        
           		if err == sql.ErrNoRows { 
        
           			c.Logger().Infof("Requested chair id \"%v\" not found", id) 
        
           			return c.NoContent(http.StatusBadRequest) 
        
           		} 
        
           		c.Logger().Errorf("Database execution error : %v", err) 
        
           		return c.NoContent(http.StatusInternalServerError) 
        
           	} 
        
           	var estates []Estate 
        
           	w := chair.Width 
        
           	h := chair.Height 
        
           	d := chair.Depth 
        
           	query = `SELECT * FROM estate WHERE (door_width >= ? AND door_height >= ?) OR (door_width >= ? AND door_height >= ?) OR (door_width >= ? AND door_height >= ?) OR (door_width >= ? AND door_height >= ?) OR (door_width >= ? AND door_height >= ?) OR (door_width >= ? AND door_height >= ?) ORDER BY popularity DESC, id ASC LIMIT ?` 
        
           	err = db.Select(&estates, query, w, h, w, d, h, w, h, d, d, w, d, h, Limit) 
        
           	if err != nil { 
        
           		if err == sql.ErrNoRows { 
        
           			return c.JSON(http.StatusOK, EstateListResponse{[]Estate{}}) 
        
           		} 
        
           		c.Logger().Errorf("Database execution error : %v", err) 
        
           		return c.NoContent(http.StatusInternalServerError) 
        
           	} 
        
           	return c.JSON(http.StatusOK, EstateListResponse{Estates: estates}) 
        
           }

なぞって検索N+1

多角形のバウンディングボックス内のやつを全部パチってから

isucon10q/isuumo/webapp/go/main.go

Lines 865 to 875 in 5b26681

    
           b := coordinates.getBoundingBox() 
        
           estatesInBoundingBox := []Estate{} 
        
           query := `SELECT * FROM estate WHERE latitude <= ? AND latitude >= ? AND longitude <= ? AND longitude >= ? ORDER BY popularity DESC, id ASC` 
        
           err = db.Select(&estatesInBoundingBox, query, b.BottomRightCorner.Latitude, b.TopLeftCorner.Latitude, b.BottomRightCorner.Longitude, b.TopLeftCorner.Longitude) 
        
           if err == sql.ErrNoRows { 
        
           	c.Echo().Logger.Infof("select * from estate where latitude ...", err) 
        
           	return c.JSON(http.StatusOK, EstateSearchResponse{Count: 0, Estates: []Estate{}}) 
        
           } else if err != nil { 
        
           	c.Echo().Logger.Errorf("database execution error : %v", err) 
        
           	return c.NoContent(http.StatusInternalServerError) 
        
           }

N+1 でポリゴン内判定を実施している

isucon10q/isuumo/webapp/go/main.go

Lines 877 to 894 in 5b26681

    
           estatesInPolygon := []Estate{} 
        
           for _, estate := range estatesInBoundingBox { 
        
           	validatedEstate := Estate{} 
        
           	point := fmt.Sprintf("'POINT(%f %f)'", estate.Latitude, estate.Longitude) 
        
           	query := fmt.Sprintf(`SELECT * FROM estate WHERE id = ? AND ST_Contains(ST_PolygonFromText(%s), ST_GeomFromText(%s))`, coordinates.coordinatesToText(), point) 
        
           	err = db.Get(&validatedEstate, query, estate.ID) 
        
           	if err != nil { 
        
           		if err == sql.ErrNoRows { 
        
           			continue 
        
           		} else { 
        
           			c.Echo().Logger.Errorf("db access is failed on executing validate if estate is in polygon : %v", err) 
        
           			return c.NoContent(http.StatusInternalServerError) 
        
           		} 
        
           	} else { 
        
           		estatesInPolygon = append(estatesInPolygon, validatedEstate) 
        
           	} 
        
           }

レスポンスの最大個数をすべての判定後に行っているので無駄がありそう

isucon10q/isuumo/webapp/go/main.go

Lines 896 to 903 in 5b26681

    
           var re EstateSearchResponse 
        
           re.Estates = []Estate{} 
        
           if len(estatesInPolygon) > NazotteLimit { 
        
           	re.Estates = estatesInPolygon[:NazotteLimit] 
        
           } else { 
        
           	re.Estates = estatesInPolygon 
        
           } 
        
           re.Count = int64(len(re.Estates))

https://dev.mysql.com/doc/refman/5.6/en/spatial-relation-functions-object-shapes.html#function_st-contains
http://next4us-ti.hatenablog.com/entry/2018/12/13/092324

server1 で nginx の LTSV ログを出した

トップページの最安の椅子、最安の物件、なんか適当にキャッシュできたりするとトップページはシュッと返せたりしそう。

stockが0にならない限りは変わることないと思って良さそう。

物件はstockの概念無いので、ハードコード出来そう。

db.SetMaxOpenConns(10)

https://qiita.com/methane/items/ccd3fd856b02b06c9452

isucon10q/isuumo/webapp/go/main.go

Line 279 in 5b26681

db.SetMaxOpenConns(10)

物件資料請求、idに紐づく物件が存在しているか見ているだけ

isucon10q/isuumo/webapp/go/main.go

Lines 927 to 936 in 5b26681

    
           estate := Estate{} 
        
           query := `SELECT * FROM estate WHERE id = ?` 
        
           err = db.Get(&estate, query, id) 
        
           if err != nil { 
        
           	if err == sql.ErrNoRows { 
        
           		return c.NoContent(http.StatusNotFound) 
        
           	} 
        
           	c.Logger().Errorf("postEstateRequestDocument DB execution error : %v", err) 
        
           	return c.NoContent(http.StatusInternalServerError) 
        
           }

Goだとメールアドレスが来ているか見ているけど、Nodeは見てなかった。この辺はステータス適切に返さないと失格になる && Goが参考実装なので、Goが正しいと思っておいて良さそう

mysql> select count(*) from estate;
+----------+
| count(*) |
+----------+
|    29500 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from chair;
+----------+
| count(*) |
+----------+
|    29500 |
+----------+
1 row in set (0.01 sec)

物件レコメンド、物件の扉のサイズ変わらないなら2回目以降は同じものを返せる？

botを弾いてベンチマークしてみたい

/api/estate� が 404 確認しに来てる

time:12/Sep/2020:04:14:42 +0000 host:10.164.40.104      request:GET /api/estate/29501 HTTP/1.1  method:GET      path:/api/estate/29501      status:404      size:0  referer:-       ua:isucon-verify        duration:0.001  apptime:0.000   connection:259      connection_requests:2   dispatch:-

とりあえず search 対象カラムに1つずつ index貼っていくのはよさそう

+-------+-----+------+-----+-----+-----+--------+-------------------------------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-----------+--------------+-----------+
| COUNT | 1XX | 2XX  | 3XX | 4XX | 5XX | METHOD |                             URI                             |  MIN  |  MAX  |   SUM   |  AVG  |  P1   |  P50  |  P99  | STDDEV | MIN(BODY) | MAX(BODY) |  SUM(BODY)   | AVG(BODY) |
+-------+-----+------+-----+-----+-----+--------+-------------------------------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-----------+--------------+-----------+
|  1079 |   0 | 1077 |   0 |   2 |   0 | GET    | GET /api/estate/search                                      | 0.039 | 0.644 | 152.334 | 0.141 | 0.136 | 0.132 | 0.168 |  0.048 |     0.000 | 21849.000 | 18022802.000 | 16703.246 |
|   794 |   0 |  791 |   0 |   3 |   0 | GET    | GET /api/chair/search                                       | 0.035 | 0.824 | 131.598 | 0.166 | 0.144 | 0.168 | 0.208 |  0.062 |     0.000 | 25095.000 | 11820223.000 | 14886.931 |
|    79 |   0 |   75 |   0 |   4 |   0 | POST   | POST /api/estate/nazotte                                    | 0.060 | 2.000 |  41.973 | 0.531 | 0.656 | 0.460 | 0.130 |  0.491 |     0.000 | 33886.000 |  1434570.000 | 18159.114 |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|   389 |   0 |  388 |   0 |   1 |   0 | GET    | GET /api/chair/low_priced                                   | 0.036 | 0.368 |  35.020 | 0.090 | 0.080 | 0.060 | 0.124 |  0.031 |     0.000 | 12153.000 |  4710016.000 | 12108.010 |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|   263 |   0 |  263 |   0 |   0 |   0 | GET    | /api/recommended_estate/.+                                  | 0.056 | 0.800 |  31.052 | 0.118 | 0.468 | 0.084 | 0.096 |  0.075 | 13067.000 | 13698.000 |  3514331.000 | 13362.475 |
|   389 |   0 |  388 |   0 |   1 |   0 | GET    | GET /api/estate/low_priced                                  | 0.024 | 0.416 |  27.134 | 0.070 | 0.080 | 0.052 | 0.072 |  0.030 |     0.000 | 13398.000 |  5192754.000 | 13348.982 |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|     1 |   0 |    1 |   0 |   0 |   0 | POST   | POST /initialize HTTP/1.1                                   | 4.208 | 4.208 |   4.208 | 4.208 | 4.208 | 4.208 | 4.208 |  0.000 |    23.000 |    23.000 |       23.000 |    23.000 |
|   664 |   0 |  663 |   0 |   1 |   0 | GET    | /api/estate/[^(nazotte|search|low_priced)]                  | 0.000 | 0.580 |   4.040 | 0.006 | 0.000 | 0.000 | 0.000 |  0.033 |   505.000 |   710.000 |   406005.000 |   611.453 |
|   267 |   0 |  265 |   0 |   2 |   0 | GET    | /api/chair/[0-9]+                                           | 0.004 | 0.188 |   2.584 | 0.010 | 0.028 | 0.000 | 0.024 |  0.019 |   463.000 |   640.000 |   143378.000 |   536.996 |
|   130 |   0 |  130 |   0 |   0 |   0 | POST   | /api/chair/buy/.+                                           | 0.004 | 0.480 |   2.124 | 0.016 | 0.000 | 0.004 | 0.008 |  0.042 |     0.000 |     0.000 |        0.000 |     0.000 |
|   350 |   0 |  350 |   0 |   0 |   0 | POST   | /api/estate/req_doc/.+                                      | 0.000 | 0.024 |   0.496 | 0.001 | 0.012 | 0.000 | 0.004 |  0.003 |     0.000 |     0.000 |        0.000 |     0.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | POST   | POST /api/chair HTTP/1.1                                    | 0.188 | 0.188 |   0.188 | 0.188 | 0.188 | 0.188 | 0.188 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|     1 |   0 |    1 |   0 |   0 |   0 | POST   | POST /api/estate HTTP/1.1                                   | 0.188 | 0.188 |   0.188 | 0.188 | 0.188 | 0.188 | 0.188 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|   181 |   0 |  181 |   0 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.008 |   0.096 | 0.001 | 0.008 | 0.000 | 0.000 |  0.001 |  2563.000 |  2563.000 |   463903.000 |  2563.000 |
|       |     |      |     |     |     |        | /api/estate/search/condition                                |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|   134 |   0 |  134 |   0 |   0 |   0 | GET    | GET                                                         | 0.004 | 0.008 |   0.064 | 0.000 | 0.000 | 0.000 | 0.000 |  0.001 |  3392.000 |  3392.000 |   454528.000 |  3392.000 |
|       |     |      |     |     |     |        | /api/chair/search/condition                                 |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|    20 |   0 |    0 |  20 |   0 |   0 | GET    | /images/chair/.+                                            | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/lcWKy9Bz0wKfiXUX6S2N7/pages/estate/detail.js  |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/lcWKy9Bz0wKfiXUX6S2N7/pages/chair/detail.js   |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|    19 |   0 |    0 |  19 |   0 |   0 | GET    | /images/estate/[0-9a-z].+                                   | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/lcWKy9Bz0wKfiXUX6S2N7/pages/estate/nazotte.js |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/lcWKy9Bz0wKfiXUX6S2N7/pages/estate/search.js  |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/lcWKy9Bz0wKfiXUX6S2N7/pages/chair/search.js   |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET /images/logo.png HTTP/1.1                               | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/lcWKy9Bz0wKfiXUX6S2N7/_buildManifest.js       |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/lcWKy9Bz0wKfiXUX6S2N7/_ssgManifest.js         |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/runtime/main-ae3d552668f1d8cb6038.js          |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/runtime/webpack-cfeea623ad98aea4381f.js       |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/lcWKy9Bz0wKfiXUX6S2N7/pages/index.js          |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|    11 |   0 |    0 |  11 |   0 |   0 | GET    | /_next/static/chunks/.+                                     | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/lcWKy9Bz0wKfiXUX6S2N7/pages/_app.js           |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
|     1 |   0 |    0 |   1 |   0 |   0 | GET    | GET                                                         | 0.000 | 0.000 |   0.000 | 0.000 | 0.000 | 0.000 | 0.000 |  0.000 |     0.000 |     0.000 |        0.000 |     0.000 |
|       |     |      |     |     |     |        | /_next/static/css/baa6e83ff6c031683f61.css                  |       |       |         |       |       |       |       |        |           |           |              |           |
|       |     |      |     |     |     |        | HTTP/1.1                                                    |       |       |         |       |       |       |       |        |           |           |              |           |
+-------+-----+------+-----+-----+-----+--------+-------------------------------------------------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-----------+--------------+-----------+

slow query https://gist.github.com/tatsuru/c74b61a4d3e25e01727b784424765008

	func searchRecommendedEstateWithChair(c echo.Context) error {
	id, err := strconv.Atoi(c.Param("id"))
	if err != nil {
	c.Logger().Infof("Invalid format searchRecommendedEstateWithChair id : %v", err)
	return c.NoContent(http.StatusBadRequest)
	}

	chair := Chair{}
	query := `SELECT * FROM chair WHERE id = ?`
	err = db.Get(&chair, query, id)
	if err != nil {
	if err == sql.ErrNoRows {
	c.Logger().Infof("Requested chair id \"%v\" not found", id)
	return c.NoContent(http.StatusBadRequest)
	}
	c.Logger().Errorf("Database execution error : %v", err)
	return c.NoContent(http.StatusInternalServerError)
	}

	var estates []Estate
	w := chair.Width
	h := chair.Height
	d := chair.Depth
	query = `SELECT * FROM estate WHERE (door_width >= ? AND door_height >= ?) OR (door_width >= ? AND door_height >= ?) OR (door_width >= ? AND door_height >= ?) OR (door_width >= ? AND door_height >= ?) OR (door_width >= ? AND door_height >= ?) OR (door_width >= ? AND door_height >= ?) ORDER BY popularity DESC, id ASC LIMIT ?`
	err = db.Select(&estates, query, w, h, w, d, h, w, h, d, d, w, d, h, Limit)
	if err != nil {
	if err == sql.ErrNoRows {
	return c.JSON(http.StatusOK, EstateListResponse{[]Estate{}})
	}
	c.Logger().Errorf("Database execution error : %v", err)
	return c.NoContent(http.StatusInternalServerError)
	}

	return c.JSON(http.StatusOK, EstateListResponse{Estates: estates})
	}

	b := coordinates.getBoundingBox()
	estatesInBoundingBox := []Estate{}
	query := `SELECT * FROM estate WHERE latitude <= ? AND latitude >= ? AND longitude <= ? AND longitude >= ? ORDER BY popularity DESC, id ASC`
	err = db.Select(&estatesInBoundingBox, query, b.BottomRightCorner.Latitude, b.TopLeftCorner.Latitude, b.BottomRightCorner.Longitude, b.TopLeftCorner.Longitude)
	if err == sql.ErrNoRows {
	c.Echo().Logger.Infof("select * from estate where latitude ...", err)
	return c.JSON(http.StatusOK, EstateSearchResponse{Count: 0, Estates: []Estate{}})
	} else if err != nil {
	c.Echo().Logger.Errorf("database execution error : %v", err)
	return c.NoContent(http.StatusInternalServerError)
	}

	estatesInPolygon := []Estate{}
	for _, estate := range estatesInBoundingBox {
	validatedEstate := Estate{}

	point := fmt.Sprintf("'POINT(%f %f)'", estate.Latitude, estate.Longitude)
	query := fmt.Sprintf(`SELECT * FROM estate WHERE id = ? AND ST_Contains(ST_PolygonFromText(%s), ST_GeomFromText(%s))`, coordinates.coordinatesToText(), point)
	err = db.Get(&validatedEstate, query, estate.ID)
	if err != nil {
	if err == sql.ErrNoRows {
	continue
	} else {
	c.Echo().Logger.Errorf("db access is failed on executing validate if estate is in polygon : %v", err)
	return c.NoContent(http.StatusInternalServerError)
	}
	} else {
	estatesInPolygon = append(estatesInPolygon, validatedEstate)
	}
	}

	var re EstateSearchResponse
	re.Estates = []Estate{}
	if len(estatesInPolygon) > NazotteLimit {
	re.Estates = estatesInPolygon[:NazotteLimit]
	} else {
	re.Estates = estatesInPolygon
	}
	re.Count = int64(len(re.Estates))

	estate := Estate{}
	query := `SELECT * FROM estate WHERE id = ?`
	err = db.Get(&estate, query, id)
	if err != nil {
	if err == sql.ErrNoRows {
	return c.NoContent(http.StatusNotFound)
	}
	c.Logger().Errorf("postEstateRequestDocument DB execution error : %v", err)
	return c.NoContent(http.StatusInternalServerError)
	}