saikuの行速度最適化(三)

前の2ラウンドの最適化を経て、saikuは使用できないので、使用できるように最適化されましたが、大量のログデータを分析するとき、トンカの感じがします!背後で実行されているSqlを引き続き観察し、インデックスに注目することにしました!
ログの主な使用シーンは、固定日付次元のデータ分析です.つまり、where条件は必ず日付がある日に等しいので、各フィールドにインデックスを作成するか、日付とインデックスを結合するかに悩んでいます.まとめると,単一フィールドのインデックス効率と連合インデックスの効率の優劣の対比である.
Postgresqlデータテーブル:saiku_search_detail
テーブル構造:

CREATE TABLE test.saiku_search_detail
(
  rpt_date date,
  from_area_id bigint,
  from_value_id bigint,
  in_track_id bigint,
  gid character varying,
  current_city_id bigint,
  dist_city_id bigint,
  category_name_id bigint,
  page_id bigint,
  utmr_page_id bigint,
  num bigint,
  id bigint,
  partner smallint
)

条数:8510490.約851万
テスト手順:
一、裸の時計
日付を問い合わせるには、次の手順に従います.
1.1単一条件

select
  count(1)
from test.saiku_search_detail
where rpt_date = '2016-05-13'

結果:1110 ms

"Aggregate  (cost=160934.85..160934.86 rows=1 width=0)"
"  ->  Seq Scan on saiku_search_detail  (cost=0.00..160816.78 rows=47230 width=0)"
"        Filter: (rpt_date = '2016-05-13'::date)"

1.2二つの条件

select
  count(1)
from test.saiku_search_detail
where rpt_date = '2016-05-13'
and from_area_id = 135

結果:1782 ms

"Aggregate  (cost=184432.32..184432.33 rows=1 width=0)"
"  ->  Seq Scan on saiku_search_detail  (cost=0.00..184431.73 rows=236 width=0)"
"        Filter: ((rpt_date = '2016-05-13'::date) AND (from_area_id = 135))"

異議なし、0インデックス!
二、二つのフィールドにそれぞれ索引を追加する:

--btree  
CREATE INDEX saiku_search_detail_from_area_id_idx
  ON saiku_search_detail
  USING btree
  (from_area_id);
--hash  
CREATE INDEX saiku_search_detail_rpt_date_idx
  ON saiku_search_detail
  USING hash
  (rpt_date);

2.1個々の条件

select
  count(1)
from saiku_search_detail
where rpt_date = '2016-05-13'

結果:83 ms

"Aggregate  (cost=8.02..8.03 rows=1 width=0)"
"  ->  Index Scan using saiku_search_detail_rpt_date_idx on saiku_search_detail  (cost=0.00..8.02 rows=1 width=0)"
"        Index Cond: (rpt_date = '2016-05-13'::date)"

インデックスを使用
2.2 2つの条件

select
  count(1)
from saiku_search_detail
where rpt_date = '2016-05-13'
and from_area_id = 135

結果:149 ms

"Aggregate  (cost=8.02..8.03 rows=1 width=0)"
"  ->  Index Scan using saiku_search_detail_rpt_date_idx on saiku_search_detail  (cost=0.00..8.02 rows=1 width=0)"
"        Index Cond: (rpt_date = '2016-05-13'::date)"
"        Filter: (from_area_id = 135)"

1つのインデックスが使用され、2つ目のインデックスは有効になりません.sqlの条件順序を変更しようとします.

select
  count(1)
from saiku_search_detail
where from_area_id = 135
and rpt_date = '2016-05-13'

結果は同じ!これはPostgresqlで、2つのインデックスフィールドを作成すると、1つだけ機能することを示しています.
三、連合インデックスの作成

--    ，         
CREATE INDEX saiku_search_detail_rpt_date_from_area_idx
  ON test.saiku_search_detail
  USING btree
  (rpt_date, from_area_id);
  

3.1単一条件クエリー&インデックス作成の最初のフィールド

select
  count(1)
from test.saiku_search_detail
where rpt_date = '2016-05-13'

結果:66 ms

"Aggregate  (cost=47843.00..47843.01 rows=1 width=0)"
"  ->  Bitmap Heap Scan on saiku_search_detail  (cost=2220.63..47362.94 rows=192025 width=0)"
"        Recheck Cond: (rpt_date = '2016-05-13'::date)"
"        ->  Bitmap Index Scan on saiku_search_detail_rpt_date_from_area_idx  (cost=0.00..2172.62 rows=192025 width=0)"

インデックスの一部が使用されていることがわかります
3.2 2つの条件クエリー

select
  count(1)
from test.saiku_search_detail
where rpt_date = '2016-05-13'
and from_area_id = 135

結果:65 ms

"Aggregate  (cost=46124.99..46125.00 rows=1 width=0)"
"  ->  Bitmap Heap Scan on saiku_search_detail  (cost=1509.67..45857.37 rows=107047 width=0)"
"        Recheck Cond: ((rpt_date = '2016-05-13'::date) AND (from_area_id = 135))"
"        ->  Bitmap Index Scan on saiku_search_detail_rpt_date_from_area_idx  (cost=0.00..1482.90 rows=107047 width=0)"

インデックスを使用
まとめ

くだらない話:2つのフィールドがフィルタ条件である場合、結合インデックスが最適です.

収益:ログ分析の過程で、日付の単一フィールドをインデックスとする以外、他の単一フィールドインデックスは役に立たず、

を削除すべきである.

葛藤:日付のみで単一のインデックスを作成するか、日付を含む複数の複合インデックスを作成するか.使用シーンによって自分で決めましょう