我有以下表格：
CREATE TABLE views (
view_id bigint NOT NULL,
usr_id bigint,
ip inet,
referer_id bigint,
country_id integer,
validated smallint,
completed smallint,
value numeric
);
ALTER TABLE ONLY views
ADD CONSTRAINT "Views_pkey" PRIMARY KEY (view_id);
CREATE TABLE country (
country_id integer NOT NULL,
country character varying(2)
);
ALTER TABLE ONLY country
ADD CONSTRAINT country_pkey PRIMARY KEY (country_id);
CREATE TABLE file_id_view_id (
file_id bigint,
view_id bigint,
created_ts timestamp without time zone
);
CREATE TABLE file_owner (
file_id bigint NOT NULL,
owner_id bigint
);
ALTER TABLE ONLY file_owner
ADD CONSTRAINT owner_table_pkey PRIMARY KEY (file_id);
CREATE TABLE referer (
referer_id bigint NOT NULL,
referer character varying(255)
);
ALTER TABLE ONLY referer
ADD CONSTRAINT referer_pkey PRIMARY KEY (referer_id);
views 和 file_id_view_id 表大约有 340M 行 each . 每小时它们都会增加 600K 行 .
file_owner 表有 75K 行，每小时会增加 100 行 .
country 表有 233 行，很少更改 .
referer 表有 6494 行，很少更改 .
我的目标是能够执行如下查询：
SELECT Count(ft.*) AS total_views,
( Count(ft.*) - SUM(ft.valid) ) AS invalid_views,
SUM(ft.valid) AS valid_views,
SUM(ft.values) AS VALUES,
ft.day AS day,
( CASE
WHEN r.referer IS NULL THEN 'Unknown'
ELSE r.referer
END ) AS referer,
( CASE
WHEN c.country IS NULL THEN 'Unknown'
ELSE c.country
END ) AS country
FROM country c
right join (referer r
right join (SELECT v.validated AS valid,
v.value AS VALUES,
vf.day AS day,
vf.view_id AS view_id,
v.referer_id AS referer_id,
v.country_id AS country_id
FROM VIEWS v,
(SELECT view_id,
fivi.created_ts :: timestamp :: DATE AS
day
FROM file_id_view_id fivi
join (SELECT file_id
FROM file_owner
WHERE owner_id = 75
GROUP BY file_id) fo
ON ( fo.file_id = fivi.file_id )
WHERE ( fivi.created_ts BETWEEN
'2015-11-01' AND '2015-12-01' )
GROUP BY view_id,
day) vf
WHERE v.view_id = vf.view_id) ft
ON ( ft.referer_id = r.referer_id ))
ON ( ft.country_id = c.country_id )
GROUP BY day,
referer,
country;
生产环境 ：
total_views | invalid_views | valid_views | values | day | referer | country
------------+---------------+-------------+--------+------------+-----------------+---------
使用 EXPLAIN ANALYZE 运行此类查询时，会生成以下内容：
GroupAggregate (cost=38893491.99..40443007.61 rows=182295955 width=52) (actual time=183725.696..205882.889 rows=172 loops=1)
Group Key: ((fivi.created_ts)::date), r.referer, c.country
-> Sort (cost=38893491.99..38984639.97 rows=182295955 width=52) (actual time=183725.655..200899.098 rows=8390217 loops=1)
Sort Key: ((fivi.created_ts)::date), r.referer, c.country
Sort Method: external merge Disk: 420192kB
-> Hash Left Join (cost=16340128.88..24989809.75 rows=182295955 width=52) (actual time=23399.900..104337.332 rows=8390217 loops=1)
Hash Cond: (v.country_id = c.country_id)
-> Hash Left Join (cost=16340125.36..24800637.72 rows=182295955 width=49) (actual time=23399.782..102534.655 rows=8390217 loops=1)
Hash Cond: (v.referer_id = r.referer_id)
-> Merge Join (cost=16340033.52..24051874.62 rows=182295955 width=29) (actual time=23397.410..99955.000 rows=8390217 loops=1)
Merge Cond: (fivi.view_id = v.view_id)
-> Group (cost=16340033.41..16716038.36 rows=182295955 width=16) (actual time=23397.298..30454.444 rows=8390217 loops=1)
Group Key: fivi.view_id, ((fivi.created_ts)::date)
-> Sort (cost=16340033.41..16434985.73 rows=189904653 width=16) (actual time=23397.294..28165.729 rows=8390217 loops=1)
Sort Key: fivi.view_id, ((fivi.created_ts)::date)
Sort Method: external merge Disk: 180392kB
-> Nested Loop (cost=6530.43..8799350.01 rows=189904653 width=16) (actual time=63.123..15131.956 rows=8390217 loops=1)
-> HashAggregate (cost=6530.31..6659.62 rows=43104 width=8) (actual time=62.983..90.331 rows=43887 loops=1)
Group Key: file_owner.file_id
-> Bitmap Heap Scan on file_owner (cost=342.90..6508.76 rows=43104 width=8) (actual time=5.407..50.779 rows=43887 loops=1)
Recheck Cond: (owner_id = 75)
Heap Blocks: exact=5904
-> Bitmap Index Scan on owner_id_index (cost=0.00..340.74 rows=43104 width=0) (actual time=4.327..4.327 rows=45576 loops=1)
Index Cond: (owner_id = 75)
-> Index Scan using file_id_view_id_indexing on file_id_view_id fivi (cost=0.11..188.56 rows=4406 width=24) (actual time=0.122..0.306 rows=191 loops=43887)
Index Cond: (file_id = file_owner.file_id)
Filter: ((created_ts >= '2015-11-01 00:00:00'::timestamp without time zone) AND (created_ts <= '2015-12-01 00:00:00'::timestamp without time zone))
Rows Removed by Filter: 184
-> Index Scan using "Views_pkey" on views v (cost=0.11..5981433.17 rows=338958763 width=25) (actual time=0.088..46804.757 rows=213018702 loops=1)
-> Hash (cost=68.77..68.77 rows=6591 width=28) (actual time=2.344..2.344 rows=6495 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 410kB
-> Seq Scan on referer r (cost=0.00..68.77 rows=6591 width=28) (actual time=0.006..1.156 rows=6495 loops=1)
-> Hash (cost=2.70..2.70 rows=233 width=7) (actual time=0.078..0.078 rows=233 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on country c (cost=0.00..2.70 rows=233 width=7) (actual time=0.005..0.042 rows=233 loops=1)
Planning time: 1.015 ms
Execution time: 206034.660 ms
(37 rows)
计划在explain.depesz.com上：http://explain.depesz.com/s/OiN
206s 运行时间 .
有些事情需要注意，
Postgresql版本 9.4
我调整了配置如下：
-
shared_buffers = 30GB
-
work_mem = 32MB
-
random_page_cost = 2.0
-
cpu_tuple_cost = 0.0030
-
cpu_index_tuple_cost = 0.0010
-
cpu_operator_cost = 0.0005
-
effective_cache_size = 52GB
目前存在以下索引：
-
CREATE INDEX country_index ON国家/地区使用btree（国家/地区）;
-
CREATE INDEX created_ts_index ON file_id_view_id使用btree（created_ts）;
-
CREATE INDEX file_id_created_ts_index ON file_id_view_id使用btree（created_ts，file_id）;
-
CREATE INDEX file_id_view_id_indexing ON file_id_view_id使用btree（file_id）;
-
CREATE INDEX owner_id_file_id_index ON file_owner使用btree（file_id，owner_id）;
-
CREATE INDEX owner_id_index ON file_owner使用btree（owner_id）;
-
CREATE INDEX referer_index ON referer使用btree（referer）;
之前的查询使用 owner id ，其被选中 conservatively ，某些查询可能导致 file_id_view_id 表的 1/3 与 views 连接 .
更改数据结构是一个 last 度假胜地 . 在这个阶段，这种变化必须引起严重关切 .
如果需要，db可以被认为是只读的，正在写入的数据是每小时完成的，并且在每次写入后给Postgres充足的喘息空间 . 在 600K hourly write 期间的当前时刻，数据库将在1100s内返回（这是由于其他原因以及插入成本） . 如果它会增加读取速度，则有足够的空间来添加附加索引，读取速度是优先级 .
硬件规格如下：
CPU：http://ark.intel.com/products/83356/Intel-Xeon-Processor-E5-2630-v3-20M-Cache-2_40-GHz
内存： 128GB
储存： 1.5TB PCIE SSD
如何优化我的数据库或查询，以便我可以在合理的时间范围内从数据库中检索出我需要的信息？
我该怎么做才能优化我目前的设计？
我相信Postgres及其运行的硬件具有比目前更好的性能 .
UPDATE
我试过了：
-
分析表格，不影响性能 .
-
增加work_mem，这导致速度增加到116s .
-
通过避免子选择来依赖Postgres的查询规划器，这会对性能产生负面影响 .
-
事先单独进行数据库查找，这似乎没有正面/负面影响 .
有没有人有重建这么大的经验？这可行吗？需要几天，几小时（当然估计）？
我正在考虑对数据库进行反规范化，因为它实际上只会在此方法中引用 . 我唯一担心的是 - 如果要从带有索引owner_id的表中调用100M行，它会足够快还是我仍然面临相同的性能问题？不愿意走一条路然后不得不回溯 .
我正在研究的另一个解决方案是@ ivan.panasuik建议，将所有日期数据分组到另一个表中，因为一旦过了一天，该信息是不变的，不需要更改或更新 . 但是我不确定如何顺利地实现这一点 - 我是否应该在插入处于暂停状态时查询数据并尽可能快地捕获日期？从那时起有一个触发器设置？
2 回答
数据库的速度通常不是您的硬件，而是您使用引擎本身的智能和功能的程度 .
尽量避免使用子选择 - 特别是在处理大量数据时 . 这些通常无法由查询计划程序优化 . 在大多数情况下，如果需要，您应该能够将简单的子选择转换为JOIN或甚至单独的数据库查找 .
对表进行分区 - PostgreSQL本身不会这样做（有点）但如果你经常只访问最近的数据，你可以删除一个通过移动存档数据来完成大量工作 .
考虑一个数据仓库策略 - 当你处理这些数据时，你应该考虑以非规范化的方式存储数据的副本，因为讨厌的JOIN已经被处理了，所以非常快速地检索 . 我们使用Redshift（PostgeSQL的派生物）来做这件事，这样我们在运行报告时就不需要做任何JOIN .
删除（Count（ft . *） - SUM（ft.valid））AS invalid_views，因为您已经拥有此值，并且您可以稍后在显示结果中进行计算
在file_owner.file_id上添加索引并检查在查询中使用的每个字段是否具有索引（您在条件中使用的字段：where，group等）
我没有更多地分析查询，但似乎你应该在一些较小（和更快）的查询中拆分查询并使用临时表或存储过程连接它 .
假设昨天的结果是不会改变的......你可以使用条件day = today（）运行查询并避免按天分组 . 全天结果您可以保存在一个单独的表中 . 我发现它大部分时间都适用于分组 .
没有尝试尝试就很难预测优化....所以一个接一个地尝试 . 还有祝你好运 .