sql - postgres : two simple, 等效查询，但一个慢得多

Question

我在亚马逊 EC2 服务器上的 ubuntu (12.04 LTS) 上运行 postgres (9.1)。我有这张 table :

    Table "public.principal_component"
    Column     |       Type       | Modifiers 
---------------+------------------+-----------
eigenvalue_id  | integer          | not null
stm_id         | integer          | not null
value          | double precision |

Indexes:
    "principal_component_pk" PRIMARY KEY, btree (eigenvalue_id, stm_id)
"pc_eigval_index" btree (eigenvalue_id)
Foreign-key constraints:
    "principal_component_eigenvalue_id_fkey" FOREIGN KEY (eigenvalue_id) REFERENCES
         eigenvalue(id)
    "principal_component_stm_id_fkey" FOREIGN KEY (stm_id) REFERENCES stm(id)

编辑:此表包含 69,789,400 行。

我尝试运行这个查询:

select count(*) from principal_component where eigenvalue_id >= 801 and 
    eigenvalue_id <= 900

但真的花了很长时间，所以我取消了。因此，我使用 bash 对上述查询范围内的每个 id 值运行查询:

time for ((a = 801; a <= 900; a++))
do 
    command="select count(*) from principal_component where eigenvalue_id=$a" 
    sudo -u postgres psql text_analytics -c "$command"`
done

这个 bash 命令总共花费了 16 秒(对于所有 100 个单独的查询加上显示等)。

然后我重新运行第一个查询，并计时:它用了 250 秒。

编辑:查询结果为 0 - 计数为 0(如我所料)

为什么会出现差异？以下是每个查询的解释计划结果:快速、个性化的查询:

explain analyze select count(*) from principal_component where eigenvalue_id = 801"
                                     QUERY PLAN                                    
-----------------------------------------------------------------------------------
Aggregate  (cost=168209.10..168209.11 rows=1 width=0) (actual time=13.367..13.369 
    rows=1 loops=1)
->  Index Scan using pc_eigval_index on principal_component  (cost=0.00..167883.16
       rows=130377 width=0) (actual time=13.344..13.344 rows=0 loops=1)
         Index Cond: (eigenvalue_id = 801)
 Total runtime: 13.512 ms
(4 rows)

缓慢的“组合”查询:

explain analyze select count(*) from principal_component where eigenvalue_id >= 801 and
    eigenvalue_id <= 900"
                                    QUERY PLAN                                         
---------------------------------------------------------------------------------------
Aggregate  (cost=1618222.49..1618222.50 rows=1 width=0) (actual time=774.585..774.586
   rows=1 loops=1)
->  Bitmap Heap Scan on principal_component  (cost=656742.39..1560409.48 rows=23125206
   width=0) (actual time=774.558..774.558 rows=0 loops=1)
     Recheck Cond: ((eigenvalue_id >= 801) AND (eigenvalue_id <= 900))
     ->  Bitmap Index Scan on pc_eigval_index  (cost=0.00..650961.09 rows=23125206 
          width=0) (actual time=774.549..774.549 rows=0 loops=1)
           Index Cond: ((eigenvalue_id >= 801) AND (eigenvalue_id <= 900))
Total runtime: 774.751 ms
(6 rows)

我对阅读计划一无所知，所以我提前为遗漏一些明显的东西道歉。提前感谢您的任何想法。

Answer 1

考虑在您的表上运行analyze，或者如果它最近进行了大规模更新则vacuum analyze，或者如果您已经这样做了则增加它正在收集的统计量。相差 2300 万行的估计有点太多了。

除此之外，Postgres 9.1 也无济于事。 9.2 将进行仅索引扫描，从而消除了过程中的位图索引扫描。

旁白:这些查询并非严格等同或可比。第一个需要改为:

select count(*) from ... group by igenvalue_id