Postgres SQL query slow with large table (AWS RDS)

Question

Currently the table have minimum row of 30million, and it is growing, whenever try to do SELECT query, it take extremely long time. What need to optimize the query before I increase the performance of the database?

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)

Please use EXPLAIN (ANALYZE, BUFFERS) rather than just plain EXPLAIN, and turn on track_io_timing. — jjanes
– jjanes, Commented Nov 24, 2021 at 4:04

The Impaler · Accepted Answer · 2021-11-24 03:41:16Z

2

Considering the specific query:

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

the following index has the potential of greatly increasing the performance:

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

However, keep in mind that creating an index on a 30-million row table can take some time.

answered Nov 24, 2021 at 3:41

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sola Over a year ago

is that mean let say i have multiple case query condition, i need to create each index for each case? "(company_id, project_id, attribute_id, snapshot_ts)", "(project_id, attribute_id, snapshot_ts)", "(attribute_id, snapshot_ts)"

jjanes Over a year ago

@sola if you need the exact optimal index for every query, then yes that can be a lot of indexes. But you probably can get away with somewhat less optimal for some of them. Try a few and see. If you have questions, be sure to include the EXPLAIN (ANALYZE, BUFFERS)

Sola Over a year ago

after create index at my local machine, and it work at the beginning, but after sometime, it did not trigger the indexes when doing query. This happened on the production server as well.

The Impaler Over a year ago

@Sola If the query is not using the index, then the optimizer it considering a different execution plan. First, make sure the statistics of the table are up to date using ANALYZE public.message. Then, if the problem still persists, please retrieve the execution plan and add it to the question.

Sola Over a year ago

thank for the reply. Yesterday when trying with index (company_id, project_id, attribute_id, snapshot_ts), in condition using (snapshot_ts and attribute_id), it working at first, then don't. Now I add another index with (attribute_id, snapshot_ts), for this purpose, seem working again, will continue monitor.

Collectives™ on Stack Overflow

Postgres SQL query slow with large table (AWS RDS)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related