sql - 执行序列扫描的 Postgres 查询优化

Question

我有一个需要 7 秒才能执行的查询。我正在尝试优化它。

这是查询:

explain analyze select count(*) from ab_view inner join ab_trial on (ab_view.trial_id = ab_trial.id) where ab_trial.variation_id = 339;
                                                                          QUERY PLAN                                                                           
---------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=91536.20..91536.21 rows=1 width=0) (actual time=6696.799..6696.800 rows=1 loops=1)
   ->  Hash Join  (cost=15844.96..91068.32 rows=187155 width=0) (actual time=557.026..6695.972 rows=670 loops=1)
         Hash Cond: (ab_view.trial_id = ab_trial.id)
         ->  Seq Scan on ab_view  (cost=0.00..36588.46 rows=2368046 width=4) (actual time=0.028..2813.947 rows=2368125 loops=1)
         ->  Hash  (cost=13743.40..13743.40 rows=128045 width=4) (actual time=516.418..516.418 rows=127671 loops=1)
               Buckets: 4096  Batches: 8  Memory Usage: 572kB
               ->  Bitmap Heap Scan on ab_trial  (cost=2868.84..13743.40 rows=128045 width=4) (actual time=21.722..302.626 rows=127671 loops=1)
                     Recheck Cond: (variation_id = 339)
                     ->  Bitmap Index Scan on ab_trial_variation_id  (cost=0.00..2836.82 rows=128045 width=0) (actual time=19.573..19.573 rows=127843 loops=1)
                           Index Cond: (variation_id = 339)
 Total runtime: 6697.140 ms
(11 rows)

这些是表定义:

                                     Table "public.ab_view"
  Column  |           Type           |                      Modifiers                       
----------+--------------------------+------------------------------------------------------
 id       | integer                  | not null default nextval('ab_view_id_seq'::regclass)
 trial_id | integer                  | not null
 datetime | timestamp with time zone | not null
Indexes:
    "ab_view_pkey" PRIMARY KEY, btree (id)
    "ab_view_trial_id_126817c891814cd7_uniq" UNIQUE CONSTRAINT, btree (trial_id, datetime)
    "ab_view_trial_id" btree (trial_id)
Foreign-key constraints:
    "trial_id_refs_id_6ea6b4c9" FOREIGN KEY (trial_id) REFERENCES ab_trial(id) DEFERRABLE INITIALLY DEFERRED

                                        Table "public.ab_trial"
    Column     |  Type   |                       Modifiers                       | Storage | Description 
---------------+---------+-------------------------------------------------------+---------+-------------
 id            | integer | not null default nextval('ab_trial_id_seq'::regclass) | plain   | 
 variation_id  | integer | not null                                              | plain   | 
 experiment_id | integer | not null                                              | plain   | 
 device_id     | integer | not null                                              | plain   | 
Indexes:
    "ab_trial_pkey" PRIMARY KEY, btree (id)
    "ab_trial_device_id_9594421392f2789_uniq" UNIQUE CONSTRAINT, btree (device_id, experiment_id)
    "ab_trial_experiment_id" btree (experiment_id)
    "ab_trial_temp_device_id" btree (device_id)
    "ab_trial_variation_id" btree (variation_id)
Foreign-key constraints:
    "device_id_refs_id_050eed1f" FOREIGN KEY (device_id) REFERENCES ab_device(id) DEFERRABLE INITIALLY DEFERRED
    "experiment_id_refs_id_ca7ef8d2" FOREIGN KEY (experiment_id) REFERENCES ab_experiment(id) DEFERRABLE INITIALLY DEFERRED
    "variation_id_refs_id_909abcec" FOREIGN KEY (variation_id) REFERENCES ab_variation(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "ab_view" CONSTRAINT "trial_id_refs_id_6ea6b4c9" FOREIGN KEY (trial_id) REFERENCES ab_trial(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "ab_conversion" CONSTRAINT "trial_id_refs_id_bf42f054" FOREIGN KEY (trial_id) REFERENCES ab_trial(id) DEFERRABLE INITIALLY DEFERRED
Has OIDs: no

我不确定如何优化它。我正在运行 Postgres 9.1。我已经看到“计数”在 9.1 上很慢，但我不确定这是我的问题。

Answer 1

表定义不存在，但看起来 ab_view 在 trial_id 上没有索引。