Postgresql
如何優化 Postgres 查詢中的排序
我一直在嘗試優化以下查詢中的排序。我跑
EXPLAIN ANALYZE了，大部分時間是在排序期間，它按距離排列輸出。我嘗試將 , 的欄位轉換
lat為lng文本並刪除該to_number函式以查看它是否會有所作為，但結果沒有改變。
ttb_members_store我嘗試在on中創建一個索引lat，lng看看這是否有幫助並且查詢慢了大約 125 毫秒。下一步是什麼？
SELECT tms.*, 0 as kyori , (power( (139.745069 - to_number(tms.lng,'000D00000000')) / 0.0111 * 1000000, 2) + power( (35.662978 - to_number(tms.lat,'000D00000000')) / 0.0091 *1000000, 2) ) AS kyori2 FROM ttb_members tms, mtb_tenshu mt WHERE (tms.tenshu_cd = mt.small_cd) AND (tms.view <> 0) AND (tms.type in (2,3)) AND (tms.delete_datetime is null) ORDER BY kyori2 ASC OFFSET 20 LIMIT 20;--------------------------------------------------------------------------- Limit (cost=7180.01..7181.66 rows=20 width=621) (actual time=615.232..615.333 rows=20 loops=1) -> Unique (cost=7178.36..8968.36 rows=21697 width=621) (actual time=615.129..615.288 rows=40 loops=1) -> Sort (cost=7178.36..7232.60 rows=21697 width=621) (actual time=615.125..615.162 rows=43 loops=1) Sort Key: ((power((((139.745069 - to_number(tms.lng, '000D00000000'::text)) / 0.0111) * 1000000::numeric), 2::num eric) + power((((35.662978 - to_number(tms.lat, '000D00000000'::text)) / 0.0091) * 1000000::numeric), 2::numeric))), tms.id, tms .store_cd, tms.store_nm_org, tms.store_nm_chg, tms.store_nm_kn_org, tms.store_nm_kn_chg, tms.address1_org, tms.address2_org, tms .address3_org, tms.address1_chg, tms.address2_chg, tms.address3_chg, tms.search_address, tms.tel, tms.kenku_cd, tms.tensyu_cd, t ms.new_flg, tms.lat, tms.lng, tms.point, tms.level, tms.view, tms.search_word, tms.create_datetime, tms.update_datetime, tms.del ete_datetime, tms.proc_flg, tms.type, tms.latlng_chg_flg, tms.view_chg_flg Sort Method: quicksort Memory: 22864kB -> Hash Join (cost=334.39..5615.61 rows=21697 width=621) (actual time=3.891..284.492 rows=25391 loops=1) Hash Cond: (tms.tensyu_cd = mt.small_cd) -> Bitmap Heap Scan on ttb_members_store tms (cost=331.97..4657.03 rows=22804 width=621) (actual time=3.6 93..31.098 rows=23141 loops=1) Recheck Cond: ((view <> 0) AND (type = ANY ('{2,3}'::integer[])) AND (delete_datetime IS NULL)) -> Bitmap Index Scan on ttb_members_store_idx3 (cost=0.00..326.27 rows=22804 width=0) (actual time= 3.206..3.206 rows=23141 loops=1) -> Hash (cost=1.63..1.63 rows=63 width=3) (actual time=0.160..0.160 rows=63 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 2kB -> Seq Scan on mtb_tensyu mt (cost=0.00..1.63 rows=63 width=3) (actual time=0.005..0.078 rows=63 lo ops=1) Total runtime: 616.526 ms下面是兩個表的定義：
桌子
ttb_members_storeColumn | Type | Modifiers -----------------+-----------------------------+----------------------------- id | integer | not null default nextval(... store_cd | character(9) | not null store_nm_org | text | store_nm_chg | text | store_nm_kn_org | text | store_nm_kn_chg | text | address1_org | text | address2_org | text | address3_org | text | address1_chg | text | address2_chg | text | address3_chg | text | search_address | text | tel | text | kenku_cd | character(4) | tensyu_cd | character(2) | new_flg | smallint | lat | text | lng | text | point | text | level | smallint | view | smallint | search_word | text | create_datetime | timestamp without time zone | not null update_datetime | timestamp without time zone | delete_datetime | timestamp without time zone | proc_flg | smallint | type | smallint | latlng_chg_flg | smallint | view_chg_flg | smallint | Indexes: "ttb_members_store_pkey" PRIMARY KEY, btree (id) "ttb_members_store_idx1" UNIQUE, btree (store_cd)桌子
mtb_tensyuColumn | Type | Modifiers -----------------+-----------------------------+----------------------------- id | integer | not null default nextval(... large_cd | integer | not null large_nm | text | not null small_cd | character(2) | not null small_nm | text | not null create_datetime | timestamp without time zone | not null default now() update_datetime | timestamp without time zone | delete_datetime | timestamp without time zone | Indexes: "mtb_tensyu_pkey" PRIMARY KEY, btree (id) "mtb_tensyu_idx1" btree (small_cd)
lat並且lng顯然是數字，因此您應該將它們儲存為適當的數字數據類型，而不是text. 這使得儲存空間更小並且不允許無效輸入，它簡化了您的查詢語法並且總體上也更快一些。不過，它不會對您的查詢有太大幫助。例如：
lat | numeric lng | numeric
mtb_tenshu商店類型也是如此。但是，如果我們可以假設參照完整性並且連接沒有排除任何行，那麼該表在所呈現的查詢中仍然只是噪音，因為您根本不使用它。所以我們有這個簡化的查詢：
SELECT tm.* -- return only columns that are needed! 0 as kyori , ((139.745069 - lng) / 0.0111 * 1000000)^2 + (( 35.662978 - lat) / 0.0091 * 1000000)^2 AS kyori2 FROM ttb_members tm WHERE view <> 0 AND type IN (2,3) AND delete_datetime IS NULL ORDER BY kyori2 OFFSET 20 LIMIT 20;既然你評論了：
所有的“AND”語句都是不變的。
您可以使用部分功能索引非常有效地支持這一點：
CREATE INDEX ttb_members_foo_idx ON ttb_members ((((139.745069 - lng) / 0.0111 * 1000000)^2 + (( 35.662978 - lat) / 0.0091 * 1000000)^2)); WHERE view <> 0 AND type IN (2,3) AND delete_datetime IS NULL;查詢中的表達式和
WHERE條件必須匹配才能使用此索引。現在 Postgres 可以跳過前 20 個索引條目，並在索引掃描中獲取接下來的 20 個很容易排序的條目。應該非常快。