我有一个巨大的表（目前大约有3mil行，预计会增加1000倍），每秒有很多插入。表从未更新。
现在我必须在那个表上运行查询，这是非常慢的（如预期的那样）。这些查询不必100%准确，如果结果是一天以前的（但不是更早的）就可以了。
目前在两个单整数列上有两个索引，我需要再添加两个索引（整数列和时间戳列）以加快查询速度。
到目前为止我的想法是：
将缺少的两个索引添加到表中
大表上根本没有索引，将内容（作为日常任务）复制到第二个表（只是重要的行），然后在第二个表上创建索引并对该表运行查询？
对大表进行分区
主/从设置（向主设备写入和从设备读取）。
就性能而言，哪种选择最好？你还有其他的建议吗？
编辑：
这是表（我已经标记了外键并对查询进行了一些修饰）：
CREATE TABLE client_log
(
id serial NOT NULL,
logid integer NOT NULL,
client_id integer NOT NULL, (FOREIGN KEY)
client_version varchar(16),
sessionid varchar(100) NOT NULL,
created timestamptz NOT NULL,
filename varchar(256),
funcname varchar(256),
linenum integer,
comment text,
domain varchar(128),
code integer,
latitude float8,
longitude float8,
created_on_server timestamptz NOT NULL,
message_id integer, (FOREIGN KEY)
app_id integer NOT NULL, (FOREIGN KEY)
result integer
);
CREATE INDEX client_log_code_idx ON client_log USING btree (code);
CREATE INDEX client_log_created_idx ON client_log USING btree (created);
CREATE INDEX clients_clientlog_app_id ON client_log USING btree (app_id);
CREATE INDEX clients_clientlog_client_id ON client_log USING btree (client_id);
CREATE UNIQUE INDEX clients_clientlog_logid_client_id_key ON client_log USING btree (logid, client_id);
CREATE INDEX clients_clientlog_message_id ON client_log USING btree (message_id);
以及一个示例查询：
SELECT
client_log.comment,
COUNT(client_log.comment) AS count
FROM
client_log
WHERE
client_log.app_id = 33 AND
client_log.code = 3 AND
client_log.client_id IN (SELECT client.id FROM client WHERE
client.app_id = 33 AND
client."replaced_id" IS NULL)
GROUP BY client_log.comment ORDER BY count DESC;
client_log_code_idx是上述查询所需的索引。还有其他查询需要客户机日志创建的idx索引。
以及查询计划：
Sort (cost=2844.72..2844.75 rows=11 width=242) (actual time=4684.113..4684.180 rows=70 loops=1)
Sort Key: (count(client_log.comment))
Sort Method: quicksort Memory: 32kB
-> HashAggregate (cost=2844.42..2844.53 rows=11 width=242) (actual time=4683.830..4683.907 rows=70 loops=1)
-> Hash Semi Join (cost=1358.52..2844.32 rows=20 width=242) (actual time=303.515..4681.211 rows=1202 loops=1)
Hash Cond: (client_log.client_id = client.id)
-> Bitmap Heap Scan on client_log (cost=1108.02..2592.57 rows=387 width=246) (actual time=113.599..4607.568 rows=6962 loops=1)
Recheck Cond: ((app_id = 33) AND (code = 3))
-> BitmapAnd (cost=1108.02..1108.02 rows=387 width=0) (actual time=104.955..104.955 rows=0 loops=1)
-> Bitmap Index Scan on clients_clientlog_app_id (cost=0.00..469.96 rows=25271 width=0) (actual time=58.315..58.315 rows=40662 loops=1)
Index Cond: (app_id = 33)
-> Bitmap Index Scan on client_log_code_idx (cost=0.00..637.61 rows=34291 width=0) (actual time=45.093..45.093 rows=36310 loops=1)
Index Cond: (code = 3)
-> Hash (cost=248.06..248.06 rows=196 width=4) (actual time=61.069..61.069 rows=105 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 4kB
-> Bitmap Heap Scan on client (cost=10.95..248.06 rows=196 width=4) (actual time=27.843..60.867 rows=105 loops=1)
Recheck Cond: (app_id = 33)
Filter: (replaced_id IS NULL)
Rows Removed by Filter: 271
-> Bitmap Index Scan on clients_client_app_id (cost=0.00..10.90 rows=349 width=0) (actual time=15.144..15.144 rows=380 loops=1)
Index Cond: (app_id = 33)
Total runtime: 4684.843 ms
最佳答案
一般来说，在一个与时间相关的数据不断被插入数据库的系统中，我建议根据时间partitioning。
这不仅是因为它可以提高查询时间，而且因为否则会使管理数据变得困难。无论您的硬件有多大，它的容量都会受到限制，因此您最终将不得不开始删除早于某个日期的行。删除行的速率必须等于它们进入的速率。
如果您只有一个大表，并且使用DELETE删除旧行，那么您将留下许多需要清空的死元组。自动真空将持续运行，耗尽宝贵的磁盘IO。
另一方面，如果根据时间进行分区，那么删除过期数据就如同删除相关子表一样简单。
在索引方面-索引不是继承的，所以您可以保存创建索引的时间，直到加载分区之后。在您的用例中，分区大小可以是1天。这意味着在插入数据时不需要不断更新索引。更实际的做法是根据需要添加索引以执行查询。
您的示例查询不在“created”时间字段上进行筛选，但您认为其他查询会进行筛选。如果按时间进行分区，并注意如何构造查询，则将启动约束排除，它将只包括与查询相关的特定分区。