Postgres SQL query缓慢的大型表(AWS RDS)

Question 1

目前该表已最低行的30million，它是不断增长，每当试图做选择的查询，它需要非常长的时间。有什么需要优化查询之前，我提高的性能数据库？

POSTGRES 12 on AWS RDS db.t3.small, with 20GB storage

**Message Table**

id (bigint) -> pk
meta (jsonb)
snapshot_ts (integer) -> epoch timestamp
value (character varying 100)
type (character varying 50)
created (timestamp with timezone)
last_modified (timestamp with timezone)
attribute_id (bigint) -> Foreign Key
company_id (bigint) -> Foreign Key
project_id (bigint) -> Foreign Key
device_id (bigint) -> Foreign Key


EXPLAIN (analyze,buffers) SELECT COUNT(*) FROM public.message
WHERE company_id=446 AND project_id=52 AND snapshot_ts>=1637568000.0 AND snapshot_ts<=1637654399.0 AND attribute_id=458

->Aggregate  (cost=399804.26..399804.27 rows=1 width=8) (actual time=65150.696..65150.697 rows=1 loops=1)
  Buffers: shared hit=170 read=115437 dirtied=167
  I/O Timings: read=64396.424
  ->  Index Scan using message_attribute_id_6578b282 on message  (cost=0.56..399803.23 rows=411 width=0) (actual time=57752.297..65147.391 rows=8656 loops=1)
        Index Cond: (attribute_id = 458)
        Filter: ((company_id = 446) AND (project_id = 52) AND ((snapshot_ts)::numeric >= 1637568000.0) AND ((snapshot_ts)::numeric <= 1637654399.0))
        Rows Removed by Filter: 106703
        Buffers: shared hit=170 read=115437 dirtied=167
        I/O Timings: read=64396.424
Planning Time: 0.779 ms
Execution Time: 65150.730 ms

**Indexes**
indexname                       | indexdef
message_attribute_id_6578b282   | CREATE INDEX message_attribute_id_6578b282 ON public.message USING btree (attribute_id)
message_company_id_cef5ed5f     | CREATE INDEX message_company_id_cef5ed5f ON public.message USING btree (company_id)
message_device_id_b4da2571      | CREATE INDEX message_device_id_b4da2571 ON public.message USING btree (device_id)
message_pkey                    | CREATE UNIQUE INDEX message_pkey ON public.message USING btree (id)
message_project_id_7ba6787d     | CREATE INDEX message_project_id_7ba6787d ON public.message USING btree (project_id)

Question 2

考虑到特定的查询:

SELECT COUNT(*)
FROM public.message
WHERE company_id=446 
  AND project_id=52 
  AND snapshot_ts>=1637568000.0 AND snapshot_ts<=1637654399.0 
  AND attribute_id=458

以下指数具有潜力大大提高的性能：

create index ix1 on public.message (
  company_id, project_id, attribute_id, snapshot_ts
);

然而，牢记，建立一个指数在30万美元的行表可能需要一些时间。

The Impaler · Answer 1 · 2021-11-24T03:41:16

2

考虑到特定的查询:

SELECT COUNT(*)
FROM public.message
WHERE company_id=446 
  AND project_id=52 
  AND snapshot_ts>=1637568000.0 AND snapshot_ts<=1637654399.0 
  AND attribute_id=458

以下指数具有潜力大大提高的性能：

create index ix1 on public.message (
  company_id, project_id, attribute_id, snapshot_ts
);

然而，牢记，建立一个指数在30万美元的行表可能需要一些时间。

The Impaler

2021-11-24 03:41:16

是那意思我说我有多情况下查询的条件，我需要建立的每个指数的每个情况下吗？ "(company_id,project_id,attribute_id,snapshot_ts)","(project_id,attribute_id,snapshot_ts)","(attribute_id,snapshot_ts)"

Sola

@索拉如果你需要准确的最佳指数的每个查询，然后是的，这可以是一个很大的索引。但你可能可以得到有点小优化其中的一些。试了几个看看。如果你有疑问，一定要包括的解释(分析、缓冲区)

jjanes

之后创建的索引我的地方机，它的工作在开始的时候，但是一段时间后，它没有触发该索引，这样做时查询。这件事发生在生产服务器上。

Sola

@Sola如果查询结果不是使用索引，则该优化它考虑到不同的执行计划。首先，确保统计数据表都是最新的使用 ANALYZE public.message. 然后，如果该问题仍然存在，请检索执行计划，并将它添加到这个问题。

The Impaler

谢谢回复。昨天在试图与指数(company_id,project_id,attribute_id,snapshot_ts)，在使用条件(snapshot_ts和attribute_id)，它的工作在第一个，则没有。现在我请加另一个指数(attribute_id,snapshot_ts)，为此目的，似乎再次合作，将继续监测。

Sola

Postgres SQL query缓慢的大型表(AWS RDS)

的问题

最好的答案

其他语言

此页面有其他语言版本

受欢迎的此类别

流行的问题，在这个类别