PostgreSQL 中 UniProt 的生物序列

Question

Mad Scientist

Asked: 2017-01-21 07:37:32 +0800 CST2017-01-21 07:37:32 +0800 CST 2017-01-21 07:37:32 +0800 CST

即使存在正确的索引，聚合列也会导致全表扫描

772

我有一个查询，我想从按 date_added 列排序的表数据集中获取前几行。排序依据的列被索引，所以这个表的基本版本非常快：

SELECT datasets.id FROM datasets ORDER BY date_added LIMIT 25

"Limit  (cost=0.28..6.48 rows=25 width=12) (actual time=0.040..0.092 rows=25 loops=1)"
"  ->  Index Scan using datasets_date_added_idx2 on datasets  (cost=0.28..1244.19 rows=5016 width=12) (actual time=0.037..0.086 rows=25 loops=1)"
"Planning time: 0.484 ms"
"Execution time: 0.139 ms"

但是一旦我使查询变得更加复杂，我就会遇到问题。我想加入另一个表示多对多关系的表并将结果聚合到一个数组列中。为此，我需要添加一个 GROUP BY id 子句：

SELECT datasets.id FROM datasets GROUP BY datasets.id ORDER BY date_added LIMIT 25

"Limit  (cost=551.41..551.47 rows=25 width=12) (actual time=9.926..9.931 rows=25 loops=1)"
"  ->  Sort  (cost=551.41..563.95 rows=5016 width=12) (actual time=9.924..9.926 rows=25 loops=1)"
"        Sort Key: date_added"
"        Sort Method: top-N heapsort  Memory: 26kB"
"        ->  HashAggregate  (cost=359.70..409.86 rows=5016 width=12) (actual time=7.016..8.604 rows=5016 loops=1)"
"              Group Key: datasets_id"
"              ->  Seq Scan on datasets  (cost=0.00..347.16 rows=5016 width=12) (actual time=0.009..1.574 rows=5016 loops=1)"
"Planning time: 0.502 ms"
"Execution time: 10.235 ms"

只需添加 GROUP BY 子句，查询现在就会对数据集表进行全面扫描，而不是像以前那样使用 date_added 列上的索引。

我想要做的实际查询的简化版本如下：

SELECT 
    datasets.id,
    array_remove(array_agg(other_table.some_column), NULL) AS other_table
FROM datasets 
LEFT JOIN other_table 
    ON other_table.id = datasets.id
GROUP BY datasets.id 
ORDER BY date_added 
LIMIT 25

为什么 GROUP BY 子句会导致索引被忽略并强制进行全表扫描？有没有办法重写此查询以使其使用其排序依据的列上的索引？

我在 Windows 上使用 Postgres 9.5.4，有问题的表目前有 5000 行，但它可能有几十万行。在 EXPLAIN ANALYZE 之前，我在两个表上手动运行了 ANALYZE。

表定义：

CREATE TABLE public.datasets
(
  id integer NOT NULL DEFAULT nextval('datasets_id_seq'::regclass),
  date_added timestamp with time zone,
  ...
  CONSTRAINT datasets_pkey PRIMARY KEY (id)
)

CREATE TABLE public.other_table
(
  id integer NOT NULL,
  some_column integer NOT NULL,
  CONSTRAINT other_table_pkey PRIMARY KEY (id, some_column)
)

\d datasets匿名化不相关列的输出：

                                                   Table "public.datasets"
             Column              |           Type           |                           Modifiers
---------------------------------+--------------------------+------------------------------------------------------
 id                              | integer                  | not null default nextval('datasets_id_seq'::regclass)
 key                             | text                     |
 date_added                      | timestamp with time zone |
 date_last_modified              | timestamp with time zone |
 *****                           | integer                  |
 ********                        | boolean                  | default false
 *****                           | boolean                  | default false
 ***************                 | integer                  |
 *********************           | integer                  |
 *********                       | boolean                  | default false
 ********                        | integer                  |
 ************                    | integer                  |
 ************                    | integer                  |
 ****************                | timestamp with time zone |
 ************                    | text                     | default ''::text
 *****                           | text                     |
 *******                         | integer                  |
 *********                       | integer                  |
 **********************          | text                     | default ''::text
 *******************             | text                     |
 ****************                | integer                  |
 **********************          | text                     | default ''::text
 *******************             | text                     | default ''::text
 **********                      | integer                  |
 ***********                     | text                     |
 ***********                     | text                     |
 **********************          | integer                  |
 ******************************* | text                     | default ''::text
 ************************        | text                     | default ''::text
 ***********                     | integer                  | default 0
 *************                   | text                     |
 *******************             | integer                  |
 ****************                | integer                  | default 0
 ***************                 | text                     |
 **************                  | text                     |
Indexes:
    "datasets_pkey" PRIMARY KEY, btree (id)
    "datasets_date_added_idx" btree (date_added)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx" btree (*)
    "datasets_*_idx1" btree (*)
    "datasets_*_idx" btree (*)

1 个回答

Voted

ypercubeᵀᴹ · Answer 1 · 2017-01-21T09:51:17+08:00

问题是您的第二个查询：

SELECT datasets.id 
FROM datasets 
GROUP BY datasets.id 
ORDER BY date_added 
LIMIT 25 ;

并不意味着你所期望的。它确实为您提供了前 25 行的排序方式，date_added因为它id是表的主键，因此GROUP BY可以在不更改结果的情况下将其删除。

然而，优化器似乎并不总是删除冗余GROUP BY，因此它会产生不同的计划。我不确定为什么 - 进行这些简化的优化器的各种功能远未涵盖所有情况。

如果您将查询更改为具有匹配和子句，您可能会得到更好的计划：GROUP BYORDER BY

SELECT d.id 
FROM datasets AS d 
GROUP BY d.date_added, d.id 
ORDER BY d.date_added, d.id 
LIMIT 25 ;

但无论如何，我的建议是“当有更简单的语法时，不要使用冗余/复杂的语法”。

现在对于第三个查询，使用连接，当GROUP BY方法工作时，您可以使用标准 SQL 窗口函数 ( ROW_NUMBER()) 或 PostgresDISTINCT ON或通过连接到派生表（它使用您的第一个查询！，更改了一些小细节）来重写它):

SELECT  
    d.id,
    array_remove(array_agg(o.some_column), NULL) AS other_table
FROM 
  ( SELECT d.id, d.date_added
    FROM datasets AS d 
    ORDER BY d.date_added 
    LIMIT 25 
  ) AS d
LEFT JOIN other_table AS o
    ON o.id = d.id
GROUP BY d.date_added, d.id
ORDER BY d.date_added
LIMIT 25 ;

我们也可以GROUP BY完全避免（好吧，它隐藏在内联子查询中）：

SELECT  
    d.id,
    ( SELECT array_remove(array_agg(o.some_column), NULL)
      FROM other_table AS o
      WHERE o.id = d.id
    ) AS other_table
FROM  datasets AS d 
ORDER BY d.date_added 
LIMIT 25 ;

编写这两个查询，以便生成的计划将首先进行（快速）限制子查询，然后进行连接，从而避免对任一表进行全表扫描。

如果您需要从更多列中聚合，第三种方法结合了上述两种方法，LATERAL在子句中使用相关 ( ) 子查询FROM：

SELECT  
    d.id,
    o.other_table
    -- more aggregates
FROM 
    ( SELECT d.id, d.date_added
      FROM datasets AS d 
      ORDER BY d.date_added 
      LIMIT 25 
    ) AS d
  LEFT JOIN LATERAL
    ( SELECT array_remove(array_agg(o.some_column), NULL) AS other_table
             -- more aggregates
      FROM other_table AS o
      WHERE o.id = d.id
    ) AS o
    ON TRUE
ORDER BY d.date_added
LIMIT 25 ;

即使存在正确的索引，聚合列也会导致全表扫描

连接到 PostgreSQL 服务器：致命：主机没有 pg_hba.conf 条目

如何让sqlplus的输出出现在一行中？

选择具有最大日期或最晚日期的日期

如何列出 PostgreSQL 中的所有模式？

列出指定表的所有列

如何在不修改我自己的 tnsnames.ora 的情况下使用 sqlplus 连接到位于另一台主机上的 Oracle 数据库

你如何mysqldump特定的表？

使用 psql 列出数据库权限

如何从 PostgreSQL 中的选择查询中将值插入表中？

如何使用 psql 列出所有数据库和表？

即使存在正确的索引，聚合列也会导致全表扫描

1 个回答

相关问题