【重新發現PostgreSQL之美】- 44 摩斯電碼

背景

場景:

某些字段的值經過計算後再過濾的場景, 例如:

json裡面的内容包含經緯度, 我們需要對經緯度進行地理資訊空間查詢過濾.

a,b,c,d分别代表語、數、英、科的分數, 查詢總分等于或範圍時, 需要計算後再搜尋.

挑戰:

大多數資料庫無法使用表達式索引, 隻能全表掃描, 逐條計算. 效率低下.

PG解決方案:

支援表達式索引(也可以叫函數索引), 性能指數級提升.

支援表達式統計資訊柱狀圖, 用于優化器計算

例子:

create table a (id int, info jsonb);    

create index idx_a on a using gist (ST_SetSRID(ST_MakePoint((info ->> 'lon')::numeric, (info ->> 'lat')::numeric),4326));    

explain select * from a order by ST_SetSRID(ST_MakePoint((info ->> 'lon')::numeric, (info ->> 'lat')::numeric),4326) <->    

ST_SetSRID(ST_MakePoint(120,70),4326) limit 10;    

 Limit (cost=0.14..0.69 rows=10 width=44)   

   -> Index Scan using idx_a on a (cost=0.14..69.40 rows=1270 width=44)   

         Order By: (st_setsrid(st_makepoint((((info ->> 'lon'::text))::numeric)::double precision, (((info ->> 'lat'::text))::numeric)::double precision), 4326) <-> '0101000020E61000000000000000005E400000000000805140'::geometry)    

create table t (id int, a float4, b float4, c float4, d float4);    

create index idx_t on t ((a+b+c+d));    

explain select * from t where a+b+c+d=400;    

 Index Scan using idx_t on t  (cost=0.15..7.99 rows=8 width=20)    

   Index Cond: ((((a + b) + c) + d) = '400'::double precision)    

postgres=# select * from pg_stats where tablename='idx_t'; 

-[ RECORD 1 ]----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  

schemaname             | public  

tablename              | idx_t  

attname                | expr  

inherited              | f  

null_frac              | 0 

avg_width              | 4  

n_distinct             | -0.9994  

most_common_vals       | {151.73206,180.91998,197.2688,200.11456,204.47366,223.13992}  

most_common_freqs      | {0.0002,0.0002,0.0002,0.0002,0.0002,0.0002} 

histogram_bounds       | {25.474722,71.74933,85.48342,93.977295,99.26418,104.8926,110.16269,114.38039,118.075554,121.30721,124.80748,127.81897,130.67479,133.32335,136.02103,138.41626,140.85258,143.05424,145.51877,147.9408,149.95238,151.72961,153.72885,155.82372,157.6345,159.48929,161.0307,162.76514,164.57907,166.19772,167.8121,169.29343,171.28735,173.25894,174.89429,176.23984,177.65022,179.2883,180.66162,182.22772,183.88147,185.28021,186.64587,188.12837,189.66924,191.4691,192.80214,194.05939,195.64655,197.10524,198.36841,199.72656,201.35751,203.02931,204.50558,205.91415,207.49933,209.28078,210.977,212.39197,214.18248,215.5002,217.03229,218.55179,220.12622,221.61935,223.03786,224.73047,226.53156,228.12646,229.62404,231.14334,232.95035,234.51816,236.07428,237.84808,239.52545,241.77795,243.91528,246.18135,248.33812,250.06604,252.14948,254.52863,257.24,260.0845,262.6031,265.53894,268.5458,271.4497,275.00317,278.2635,281.67947,286.42548,290.9062,295.2775,301.70978,307.6002,317.32483,328.39,375.6833}  

correlation            | -0.026684083  

most_common_elems      |  

most_common_elem_freqs |   

elem_count_histogram   |