logo标签列表

PostgreSQL简单JOIN非常慢

performancepostgresqljoin
11
我有一个简单的查询，和两个表： drilldown 
CREATE SEQUENCE drilldown_id_seq;

CREATE TABLE drilldown (
    transactionid bigint NOT NULL DEFAULT nextval('drilldown_id_seq'),
    userid bigint NOT NULL default 0 REFERENCES users(id),
    pathid bigint NOT NULL default 0,
    reqms bigint NOT NULL default 0,
    quems bigint NOT NULL default 0,
    clicktime timestamp default current_timestamp,
    PRIMARY KEY(transactionid)
);

ALTER SEQUENCE drilldown_id_seq OWNED BY drilldown.transactionid;

CREATE INDEX drilldown_idx1 ON drilldown (clicktime);

querystats

CREATE SEQUENCE querystats_id_seq;

CREATE TABLE querystats (
    id bigint NOT NULL DEFAULT nextval('querystats_id_seq'),
    transactionid bigint NOT NULL default 0 REFERENCES drilldown(transactionid),
    querynameid bigint NOT NULL default 0 REFERENCES queryname(id),
    queryms bigint NOT NULL default 0,
    PRIMARY KEY(id)
);

ALTER SEQUENCE querystats_id_seq OWNED BY querystats.id;

CREATE INDEX querystats_idx1 ON querystats (transactionid);
CREATE INDEX querystats_idx2 ON querystats (querynameid);

drilldown有150万条记录，而querystats有1000万条记录；当我在两者之间进行连接时出现了问题。

查询

explain analyse
select avg(qs.queryms)
  from querystats qs
  join drilldown d on (qs.transactionid=d.transactionid)
  where querynameid=1;

查询计划

聚合（成本=528596.96..528596.97 行=1 宽度=8）（实际时间=5213.154..5213.154 行=1 循环=1） -> 哈希连接（成本=274072.53..518367.59 行=4091746 宽度=8）（实际时间=844.087..3528.788 行=4117717 循环=1） 哈希条件：（qs.transactionid = d.transactionid） -> 位图堆扫描在querystats qs上（成本=88732.62..210990.44 行=4091746 宽度=16）（实际时间=309.502..1321.029 行=4117717 循环=1） 重新检查条件：（querynameid = 1） -> 位图索引扫描在querystats_idx2上（成本=0.00..87709.68 行=4091746 宽度=0）（实际时间=307.916..307.916 行=4117718 循环=1） 索引条件：（querynameid = 1） -> 哈希（成本=162842.29..162842.29 行=1371250 宽度=8）（实际时间=534.065..534.065 行=1372574 循环=1） 桶：4096 批次：64 内存使用：850kB -> 使用drilldown_pkey索引扫描在drilldown d上（成本=0.00..162842.29 行=1371250 宽度=8）（实际时间=0.015..364.657 行=1372574 循环=1） 总运行时间：5213.205毫秒 （11行）
我知道在PostgreSQL中有一些可以调整的调优参数，但我想知道的是我正在执行的查询是否以最优的方式连接了这两个表？
或者可能是某种内连接？我不确定。
非常感谢任何指导！
database#\d drilldown
                                       Table "public.drilldown"
    Column     |            Type             |                       Modifiers                        
---------------+-----------------------------+--------------------------------------------------------
 transactionid | bigint                      | not null default nextval('drilldown_id_seq'::regclass)
 userid        | bigint                      | not null default 0
 pathid        | bigint                      | not null default 0
 reqms         | bigint                      | not null default 0
 quems         | bigint                      | not null default 0
 clicktime     | timestamp without time zone | default now()
Indexes:
    "drilldown_pkey" PRIMARY KEY, btree (transactionid)
    "drilldown_idx1" btree (clicktime)
Foreign-key constraints:
    "drilldown_userid_fkey" FOREIGN KEY (userid) REFERENCES users(id)
Referenced by:
    TABLE "querystats" CONSTRAINT "querystats_transactionid_fkey" FOREIGN KEY (transactionid) REFERENCES drilldown(transactionid)

database=# \d querystats
                            Table "public.querystats"
    Column     |  Type  |                        Modifiers                        
---------------+--------+---------------------------------------------------------
 id            | bigint | not null default nextval('querystats_id_seq'::regclass)
 transactionid | bigint | not null default 0
 querynameid   | bigint | not null default 0
 queryms       | bigint | not null default 0
Indexes:
    "querystats_pkey" PRIMARY KEY, btree (id)
    "querystats_idx1" btree (transactionid)
    "querystats_idx2" btree (querynameid)
Foreign-key constraints:
    "querystats_querynameid_fkey" FOREIGN KEY (querynameid) REFERENCES queryname(id)
    "querystats_transactionid_fkey" FOREIGN KEY (transactionid) REFERENCES drilldown(transactionid)

所以这里是所请求的两个表格和版本。
PostgreSQL 9.1.7 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit

这个查询的作用是获取每个查询类型（querynameid）的所有行的queryms值的平均值。
            name            |         current_setting          |        source        
----------------------------+----------------------------------+----------------------
 application_name           | psql                             | client
 client_encoding            | UTF8                             | client
 DateStyle                  | ISO, MDY                         | configuration file
 default_text_search_config | pg_catalog.english               | configuration file
 enable_seqscan             | off                              | session
 external_pid_file          | /var/run/postgresql/9.1-main.pid | configuration file
 lc_messages                | en_US.UTF-8                      | configuration file
 lc_monetary                | en_US.UTF-8                      | configuration file
 lc_numeric                 | en_US.UTF-8                      | configuration file
 lc_time                    | en_US.UTF-8                      | configuration file
 log_line_prefix            | %t                               | configuration file
 log_timezone               | localtime                        | environment variable
 max_connections            | 100                              | configuration file
 max_stack_depth            | 2MB                              | environment variable
 port                       | 5432                             | configuration file
 shared_buffers             | 24MB                             | configuration file
 ssl                        | on                               | configuration file
 TimeZone                   | localtime                        | environment variable
 unix_socket_directory      | /var/run/postgresql              | configuration file
(19 rows)

我看到enable_seqscan=off，我没有改动任何设置，这是一个完全默认的安装。
更新：
我根据下面的评论做了一些更改，这是结果。
explain analyse 
SELECT 
(
  SELECT avg(queryms) AS total 
  FROM querystats 
  WHERE querynameid=3
) as total 
FROM querystats qs 
JOIN drilldown d ON (qs.transactionid=d.transactionid) 
WHERE qs.querynameid=3 
limit 1;

查询计划
    ---------------------------------------------------------------------------------------------------------------------------------------------------------
     Limit  (cost=196775.99..196776.37 rows=1 width=0) (actual time=2320.876..2320.876 rows=1 loops=1)
       InitPlan 1 (returns $0)
         ->  Aggregate  (cost=196775.94..196775.99 rows=1 width=8) (actual time=2320.815..2320.815 rows=1 loops=1)
               ->  Bitmap Heap Scan on querystats  (cost=24354.25..189291.69 rows=2993698 width=8) (actual time=226.516..1144.690 rows=2999798 loops=1)
                     Recheck Cond: (querynameid = 3)
                     ->  Bitmap Index Scan on querystats_idx  (cost=0.00..23605.83 rows=2993698 width=0) (actual time=225.119..225.119 rows=2999798 loops=1)
                           Index Cond: (querynameid = 3)
       ->  Nested Loop  (cost=0.00..1127817.12 rows=2993698 width=0) (actual time=2320.876..2320.876 rows=1 loops=1)
             ->  Seq Scan on drilldown d  (cost=0.00..76745.10 rows=1498798 width=8) (actual time=0.009..0.009 rows=1 loops=1)
             ->  Index Scan using querystats_idx on querystats qs  (cost=0.00..0.60 rows=2 width=8) (actual time=0.045..0.045 rows=1 loops=1)
                   Index Cond: ((querynameid = 3) AND (transactionid = d.transactionid))
     Total runtime: 2320.940 ms
    (12 rows)
感谢您展示查询计划；+1好问题。最好也包括您的Pg版本。考虑在psql中显示感兴趣的表的\d输出，这样我们可以看到存在哪些索引、表定义等信息。 - Craig Ringer
我回家后会添加你需要的内容，谢谢。 - MichaelM
5个回答
10

它的行为就像你设置了enable_seqscan = off，因为它正在使用索引扫描来填充哈希表。除非作为诊断步骤，否则不要关闭任何规划器选项，如果您正在显示计划，请显示使用的任何选项。可以运行此命令以显示许多有用的信息：

SELECT version();
SELECT name, current_setting(name), source
  FROM pg_settings
  WHERE source NOT IN ('default', 'override');

如果您告诉我们运行时环境的情况，特别是机器上的RAM数量，存储系统的外观以及数据库的大小（或者更好的是数据库中频繁引用数据的活动数据集），也有助于解决问题。

大致而言，5.2秒的时间可以分为：

  1. 1.3秒用于查找符合选择标准的4,117,717个querystats行。
  2. 2.3秒用于随机匹配这些行与drilldown记录。
  3. 1.6秒用于传递4,117,717行并计算平均值。

因此，即使它似乎无法使用最快的计划，但每定位一行，将其连接到另一个并将其纳入平均值计算中仅需要1.26微秒（百万分之一秒）。就绝对基础而言，这还不错，但您几乎可以肯定地获得一个稍微更快的计划。

首先，如果您正在使用9.2.x，其中x小于3，请立即升级到9.2.3。最近的版本修复了某些计划类型的性能回归问题，可能会影响此查询。一般来说，尝试保持最新的次要版本（版本号在第二个点之后更改）。

您可以通过仅在该连接上设置计划因素并运行查询（或对其进行EXPLAIN）来在单个会话中测试不同的计划。尝试类似于以下内容：

SET seq_page_cost = 0.1;
SET random_page_cost = 0.1;
SET cpu_tuple_cost = 0.05;
SET effective_cache_size = '3GB'; -- actually use shared_buffers plus OS cache

确保所有enable_设置都是on。

enable_seqscan 的优化很棒！ - Erwin Brandstetter
3
您在问题中提到：

我看到enable_seqscan=off，我没有更改任何设置，这是完全默认的安装。

相比之下，从pg_settings的输出可以看出：

enable_seqscan | off | session

这意味着您在会话中设置了enable_seqscan = off。这里有些不符合逻辑。
运行：
SET enable_seqscan = on;

或者
RESET enable_seqscan;

断言：

SHOW enable_seqscan;

此外，您的shared_buffers设置对于拥有数百万条记录的数据库来说太低了。Ubuntu默认保守设置为24MB。如果需要进行严肃的使用，您需要编辑配置文件！引用手册中的一段话：

如果您有一台专用的数据库服务器，具有1GB或更多的RAM，则shared_buffers的合理起始值为系统内存的25％。

因此，请编辑您的postgresql.conf文件以增加该值并重新加载。
然后再次尝试您的查询，并找出如何关闭了enable_seqscan。
在你的第一篇帖子之后，我确实将 enable_seqscan = on 进行了更改，至于我所谓的“声称”，我没有触及此安装的任何配置设置。感谢您的帮助。 - MichaelM
1
在这个查询中
select avg(qs.queryms) 
from querystats qs 
join drilldown d 
  on (qs.transactionid=d.transactionid) 
where querynameid=1;

您没有使用“drilldown”表中的任何列。由于外键约束保证了“querystats”中的每个“transactionid”都有一行在“drilldown”中，我认为连接不会产生任何有用的结果。除非我错过了什么，否则您的查询等效于

select avg(qs.queryms) 
from querystats qs 
where querynameid=1;

完全不需要加入(join)。只要在“querynameid”上建立索引，您应该能够获得良好的性能。

我实际上正在使用钻取中的一列，我已经将其删除，但它是d.transactionid，它连接另一个表。我已经排除了这个问题，因为我已经将其消除。只有在这个连接时才会出现减速，否则第二个查询您提到的是即时的。 - MichaelM
3
@MichaelM：请在您的问题中提供完整的情况描述。过于简化会掩盖您实际的问题。您所呈现的查询只返回一行数据。 - Erwin Brandstetter
我已经更新了我的问题：是的，只应该返回一行。 - MichaelM
1
当您不进行连接时，avg(qs.queryms)只会执行一次。
当您执行连接时，avg(qs.queryms)将根据连接生成的行数执行相应次数。
如果您始终只对单个querynameid感兴趣，请尝试将avg(qs.queryms)放入子查询中：
SELECT 
    (SELECT avg(queryms) FROM querystats WHERE querynameid=1) 
FROM querystats qs 
JOIN drilldown d ON (qs.transactionid=d.transactionid) 
WHERE qs.querynameid=1;
1
我不这么认为。表“drilldown”处于与“querystats”的1：M关系的一侧。无论是否进行连接，您都将获得“querystats”中所有相关行的平均值，“drilldown”没有任何贡献。 - Mike Sherrill 'Cat Recall'
1

在我的看法中，querystats表看起来像是一个臃肿的连接表。如果是这种情况：省略代理键，使用自然（复合）键（两个组件已经不可为空），并添加反向的复合索引。（单独的索引是无用的，外键约束会自动为您生成它们）

-- CREATE SEQUENCE querystats_id_seq;

CREATE TABLE querystats (
    -- id bigint NOT NULL DEFAULT nextval('querystats_id_seq'),
    transactionid bigint NOT NULL default 0 REFERENCES drilldown(transactionid),
    querynameid bigint NOT NULL default 0 REFERENCES queryname(id),
    queryms bigint NOT NULL default 0,
    PRIMARY KEY(transactionid,querynameid )
);

-- ALTER SEQUENCE querystats_id_seq OWNED BY querystats.id;

--CREATE INDEX querystats_idx1 ON querystats (transactionid);
-- CREATE INDEX querystats_idx2 ON querystats (querynameid);
CREATE UNIQUE INDEX querystats_alt ON querystats (querynameid, transactionid);
这不起作用，因为此表中的querynameid和transactionid可能具有重复的ID。 - MichaelM
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接