postgresql - 为什么在 PostgreSQL 上启用 intarray 扩展会导致 10 倍的性能下降？

这是我运行的日志。
关闭 intarray 后，它的运行时间为 56 毫秒。
然后我启用 intarray，并运行相同的查询，但速度要慢得多。

启用扩展后，我是否需要重新索引表或类似的东西？

test_int=# explain analyze select * from tutor_topic tt1 where tt1.topic_id @> '{5,7,8,9}';
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Seq Scan on tutor_topic tt1  (cost=0.00..29742.00 rows=1000 width=105) (actual time=24.903..1937.480 rows=68 loops=1)
   Filter: (topic_id @> '{5,7,8,9}'::integer[])
   Rows Removed by Filter: 999932
 Planning time: 0.084 ms
 Execution time: 1937.521 ms
(5 rows)

Time: 1938.000 ms
test_int=# DROP EXTENSION intarray;                                                                                                                              DROP EXTENSION
Time: 10.516 ms
test_int=# explain analyze select * from tutor_topic tt1 where tt1.topic_id @> '{5,7,8,9}';
                                                            QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on tutor_topic tt1  (cost=108.78..487.18 rows=100 width=105) (actual time=55.063..55.138 rows=68 loops=1)
   Recheck Cond: (topic_id @> '{5,7,8,9}'::integer[])
   Heap Blocks: exact=68
   ->  Bitmap Index Scan on message_rdtree_idx  (cost=0.00..108.75 rows=100 width=0) (actual time=55.047..55.047 rows=68 loops=1)
         Index Cond: (topic_id @> '{5,7,8,9}'::integer[])
 Planning time: 0.196 ms
 Execution time: 55.180 ms
(7 rows)

Time: 56.095 ms
test_int=#

这是tutor_topic 表的模式；

CREATE TABLE IF NOT EXISTS tutor_topic
(
      tutor_id              INT NOT NULL,
      topic_id              INT[]
);

这些是指数:

ALTER TABLE tutor_topic ADD FOREIGN KEY (tutor_id) REFERENCES tutor(tutor_id);
CREATE INDEX ON tutor_topic (tutor_id);
CREATE INDEX message_rdtree_idx ON tutor_topic USING GIN (topic_id)

 Schema |           Name            | Type  | Owner  |    Table
--------+---------------------------+-------+--------+-------------
 public | message_rdtree_idx        | index | xxxxxx | tutor_topic
 public | topic_pkey                | index | xxxxxx | topic
 public | tutor_pkey                | index | xxxxxx | tutor
 public | tutor_topic_tutor_id_idx  | index | xxxxxx | tutor_topic
 public | tutor_topic_tutor_id_idx1 | index | xxxxxx | tutor_topic

最佳答案

我想出了答案!

创建索引时，我需要从扩展中指定函数:
CREATE INDEX message_rdtree_idx ONtutor_topic 使用 GIN (topic_id gin__int_ops);

现在性能匹配。从解释分析调用来看，它似乎根本没有使用索引。

关于postgresql - 为什么在 PostgreSQL 上启用 intarray 扩展会导致 10 倍的性能下降？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/37769246/