在Postgres 9.1上更新查询太慢
我的问题是,我有一个非常缓慢的更新查询在一个包含1400万行的表上。我尝试了不同的事情来调整我的服务器,它带来了良好的性能,但不适用于更新查询。在Postgres 9.1上更新查询太慢
我有两个表:
- T1具有4个列和3个索引它(530行)
- T2具有15列和在其上3个索引(14个百万行)
- 我想通过在文本字段stxt中加入两个表来更新T2中字段vid(类型整数)的相同T1值。
这里是我的查询和输出:
explain analyse
update T2
set vid=T1.vid
from T1
where stxt2 ~ stxt1 and T2.vid = 0;
Update on T2 (cost=0.00..9037530.59 rows=2814247 width=131) (actual time=25141785.741..25141785.741 rows=0 loops=1) -> Nested Loop (cost=0.00..9037530.59 rows=2814247 width=131) (actual time=32.636..25035782.995 rows=679354 loops=1) Join Filter: ((T2.stxt2)::text ~ (T1.stxt1)::text) -> Seq Scan on T2 (cost=0.00..594772.96 rows=1061980 width=121) (actual time=0.067..5402.614 rows=1037809 loops=1) Filter: (vid= 1) -> Materialize (cost=0.00..17.95 rows=530 width=34) (actual time=0.000..0.069 rows=530 loops=1037809) -> Seq Scan on T1 (cost=0.00..15.30 rows=530 width=34) (actual time=0.019..0.397 rows=530 loops=1) Total runtime: 25141785.904 ms
正如你可以看到查询了约25141秒(约7小时)。如果我理解的很好,计划者估计执行时间为9037秒(〜2.5小时)。我在这里错过了什么吗?
这里是我的服务器配置信息:
- 的CentOS 5.8,20GB的RAM
- 的shared_buffers = 12GB
- work_mem = 64MB
- maintenance_work_mem = 64MB
- bgwriter_lru_maxpages = 500
- checkpoint_segments = 64
- checkpoint_completion_target = 0.9
- effective_cache_size = 10GB
我已经运行满真空和表T2分析多次但仍没有太大改善的情况。 PS:如果我将full_page_writes设置为关闭,这将大大改善更新查询,但我不想冒数据丢失的风险。你有任何建议吗?
这不是一个解决方案,但是一个数据的建模工作周围
- 分手的URL转换成{协议,主机名,路径名}组件。
- 现在,您可以使用完全匹配来加入主机名部分,避免了正则表达式匹配中的前导%。
- 该视图旨在证明full_url可以根据需要进行重构。
更新可能需要几分钟的时间。
SET search_path='tmp';
DROP TABLE urls CASCADE;
CREATE TABLE urls
(id SERIAL NOT NULL PRIMARY KEY
, full_url varchar
, proto varchar
, hostname varchar
, pathname varchar
);
INSERT INTO urls(full_url) VALUES
('ftp://www.myhost.com/secret.tgz')
,('http://www.myhost.com/robots.txt')
,('http://www.myhost.com/index.php')
,('https://www.myhost.com/index.php')
,('http://www.myhost.com/subdir/index.php')
,('https://www.myhost.com/subdir/index.php')
,('http://www.hishost.com/index.php')
,('https://www.hishost.com/index.php')
,('http://www.herhost.com/index.php')
,('https://www.herhost.com/index.php')
;
UPDATE urls
SET proto = split_part(full_url, '://' , 1)
, hostname = split_part(full_url, '://' , 2)
;
UPDATE urls
SET pathname = substr(hostname, 1+strpos(hostname, '/'))
, hostname = split_part(hostname, '/' , 1)
;
-- the full_url field is now redundant: we can drop it
ALTER TABLE urls
DROP column full_url
;
-- and we could always reconstruct the full_url from its components.
CREATE VIEW vurls AS (
SELECT id
, proto || '://' || hostname || '/' || pathname AS full_url
, proto
, hostname
, pathname
FROM urls
);
SELECT * FROM urls;
;
SELECT * FROM vurls;
;
OUTPUT:
INSERT 0 10
UPDATE 10
UPDATE 10
ALTER TABLE
CREATE VIEW
id | proto | hostname | pathname
----+-------+-----------------+------------------
1 | ftp | www.myhost.com | secret.tgz
2 | http | www.myhost.com | robots.txt
3 | http | www.myhost.com | index.php
4 | https | www.myhost.com | index.php
5 | http | www.myhost.com | subdir/index.php
6 | https | www.myhost.com | subdir/index.php
7 | http | www.hishost.com | index.php
8 | https | www.hishost.com | index.php
9 | http | www.herhost.com | index.php
10 | https | www.herhost.com | index.php
(10 rows)
id | full_url | proto | hostname | pathname
----+-----------------------------------------+-------+-----------------+------------------
1 | ftp://www.myhost.com/secret.tgz | ftp | www.myhost.com | secret.tgz
2 | http://www.myhost.com/robots.txt | http | www.myhost.com | robots.txt
3 | http://www.myhost.com/index.php | http | www.myhost.com | index.php
4 | https://www.myhost.com/index.php | https | www.myhost.com | index.php
5 | http://www.myhost.com/subdir/index.php | http | www.myhost.com | subdir/index.php
6 | https://www.myhost.com/subdir/index.php | https | www.myhost.com | subdir/index.php
7 | http://www.hishost.com/index.php | http | www.hishost.com | index.php
8 | https://www.hishost.com/index.php | https | www.hishost.com | index.php
9 | http://www.herhost.com/index.php | http | www.herhost.com | index.php
10 | https://www.herhost.com/index.php | https | www.herhost.com | index.php
(10 rows)
现在尝试运行这两个更新以获取临时表中的{protocol,hostname,pathname}组件。不要删除full_url字段。 – wildplasser 2012-07-08 16:21:40
我有一个帖子的答案,但我必须等待27分钟:)。我是新的,没有足够的声誉 – datatanger 2012-07-08 16:35:20
顺便说一句:你可以使用上述临时表作为联结表;将full_url翻译为(canonical?)主机名。 – wildplasser 2012-07-08 16:38:15
谢谢你,这会带来一些帮助。因此,这里是我所做的:
- 我创建的表的URL正如你所提到
- 我已经添加了整数类型的VID列到它
- 我插入了T2
- 在full_url列百万行我启用时间,并更新了full_url hostname列不包含既不是 'HTTP' 也不是 'WWW'
update urls set hostname=full_url where full_url not like '%/%' and full_url not like 'www\.%';
Time: 112435.192 ms
然后我运行此查询:
mydb=> explain analyse update urls set vid=vid from T1 where hostname=stxt1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Update on urls (cost=21.93..37758.76 rows=864449 width=124) (actual time=767.793..767.793 rows=0 loops=1)
-> Hash Join (cost=21.93..37758.76 rows=864449 width=124) (actual time=102.324..430.448 rows=94934 loops=1)
Hash Cond: ((urls.hostname)::text = (T1.stxt1)::text)
-> Seq Scan on urls (cost=0.00..25612.52 rows=927952 width=114) (actual time=0.009..265.962 rows=927952 loops=1)
-> Hash (cost=15.30..15.30 rows=530 width=34) (actual time=0.444..0.444 rows=530 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 35kB
-> Seq Scan on T1 (cost=0.00..15.30 rows=530 width=34) (actual time=0.002..0.181 rows=530 loops=1)
Total runtime: 767.860 ms
我是真的总运行时间感到惊讶!少于1秒这确认了你说的有关完全匹配更新的内容。
mydb=> select count(*) from T2 where vid is null and exists(select null from T1 where stxt1=stxt2);
count
--------
308486
(1 row)
因此我尝试了T2表的更新,并得到这个:
mydb=> explain analyse update T2 set vid = T1.vid from T1 where T2.vid is null and stxt2=stxt1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Update on T2 (cost=21.93..492023.13 rows=2106020 width=131) (actual time=252395.118..252395.118 rows=0 loops=1)
-> Hash Join (cost=21.93..492023.13 rows=2106020 width=131) (actual time=1207.897..4739.515 rows=308486 loops=1)
Hash Cond: ((T2.stxt2)::text = (T1.stxt1)::text)
-> Seq Scan on T2 (cost=0.00..455452.09 rows=4130377 width=121) (actual time=158.773..3915.379 rows=4103865 loops=1)
Filter: (vid IS NULL)
-> Hash (cost=15.30..15.30 rows=530 width=34) (actual time=0.293..0.293 rows=530 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 35kB
-> Seq Scan on T1 (cost=0.00..15.30 rows=530 width=34) (actual time=0.005..0.121 rows=530 loops=1)
Total runtime: 252395.204 ms
(9 rows)
Time: 255389.704 ms
其实255秒似乎是一个很好的时间,现在我搜索需要付出xtxt1和stxt2这种方式之间匹配对于这样的查询。我会尝试从所有网址中提取主机名部分并进行更新。我仍然应该确保使用精确匹配进行更新的速度很快,因为我对它有不好的经验。
感谢您的支持。
下面是我之前对功能索引的评论的一个扩展示例。如果你使用postgresql并且不知道函数索引是什么,那么你可能正因为它而感到痛苦。
让我们创建一个测试表放入一些数据吧:
smarlowe=# create table test (a text, b text, c int);
smarlowe=# insert into test select 'abc','z',0 from generate_series(1,1000000); -- 1 million rows that don't match
smarlowe=# insert into test select 'abc','a',0 from generate_series(1,10); -- 10 rows that do match
smarlowe=# insert into test select 'abc','z',1 from generate_series(1,1000000); -- another million rows that won't match.
现在,我们要在其上运行一些查询测试:
\timing
select * from test where a ~ b and c=0; -- ignore how long this takes
select * from test where a ~ b and c=0; -- run it twice to get a read with cached data.
在我的笔记本电脑这大约需要750毫秒。这种基于C经典指数:
smarlowe=# create index test_c on test(c);
smarlowe=# select * from test where a ~ b and c=0;
发生在我的笔记本电脑〜400毫秒。
此功能指数寿:
smarlowe=# drop index test_c ;
smarlowe=# create index test_regex on test (c) where (a~b);
smarlowe=# select * from test where a ~ b and c=0;
现在运行在1.3ms。
当然,没有免费午餐这样的东西,您将在更新/插入期间为此索引付款。
当然有一定的代价,但在你的例子中有一个选择性的'WHERE'子句的部分索引是相当便宜的,并且会导致一个很小的索引。很有用。 – 2012-07-11 21:21:54
@Scott Marlowe谢谢你的提示。所以功能性索引是部分索引?我想我已经在官方文档中阅读过关于它们的内容,但从未使用它们。我不知道,但我thnik他们只在一些specefic情况下有用。 – datatanger 2012-07-12 08:58:21
尝试使用MERGE代替。它可以更快地链接表格。 – Samson 2012-07-08 09:15:33
你真的需要〜操作符吗? stxt1,stxt2字段中有什么,以及它们的类型是什么? – wildplasser 2012-07-08 10:15:50
@wildplasser〜运算符几乎等同于'stxt2',如'%'|| stxt1 ||'%''。两个字段stxt都是字符变化的。 @radashk我试过这个[link](http://petereisentraut.blogspot.com/2010/05/merge-syntax.html),但是Postgres总是告诉我错误:语法错误处于或接近“MERGE”。我该如何尝试“合并”? – datatanger 2012-07-08 10:27:36