postgresql - Postgres 对多列的慢速不同查询

Question

我有一个非常简单的查询，但运行时间太长。

SELECT DISTINCT col1,col2,col3,col4 FROM tbl1;

我需要加什么索引来提速？我运行了一个简单的 vacuum; 命令并添加了以下索引，但都没有帮助。

CREATE INDEX tbl_idx ON tbl1(col1,col2,col3,col4);

该表有 40 万行。事实上，计算它们也需要很长时间。运行一个简单的

SELECT count(*) from tbl1;

需要 8 秒。所以我的问题可能出在吸尘或重建索引或我不确定的事情上。

这是解释命令

EXPLAIN SELECT DISTINCT col1,col2,col3,col4 FROM tbl1;
                                   QUERY PLAN                                    
---------------------------------------------------------------------------------
 Unique  (cost=3259846.80..3449267.51 rows=137830 width=25)
   ->  Sort  (cost=3259846.80..3297730.94 rows=15153657 width=25)
         Sort Key: col1, col2, col3, col4
         ->  Seq Scan on tbl1 (cost=0.00..727403.57 rows=15153657 width=25)
(4 rows)

编辑:我目前正在运行 vacuum full;，这有望解决问题，然后也许有人可以就如何解决我出错的地方提供一些指导。据我所知，已经有几个小时了，而且还在继续。我确实跑了

select relname, last_autoanalyze, last_autovacuum, last_vacuum, n_dead_tup from pg_stat_all_tables where n_dead_tup >0;

并且该表有将近 1600 万行 n_dead_tup。

Answer 1

我的数据不会经常更改，所以我最终创建了一个物化 View

CREATE MATERIALIZED VIEW tbl1_distinct_view AS SELECT DISTINCT col1,col2,col3,col4 FROM tbl1;

我每天早上 6 点用 cronjob 刷新一次

0 6 * * * psql -U mydb mydb -c 'REFRESH MATERIALIZED VIEW tbl1_distinct_view;