Slow simple select query on Postgresql 13 RDS hosted indexed table

Question

I am working with a simple table, hosted on RDS instance running Postgresql 13. It is using t3.small instance with general purpose SSD storage.

The table and its index look like this

measurements=> \d+ measurement_export_2 
                                         Table "public.measurement_export_2"
      Column       |           Type           | Collation | Nullable | Default | Storage | Stats target | Description 
-------------------+--------------------------+-----------+----------+---------+---------+--------------+-------------
 metering_point_id | integer                  |           |          |         | plain   |              | 
 datetime_to       | timestamp with time zone |           |          |         | plain   |              | 
 energy            | real                     |           |          |         | plain   |              | 
 is_consumption    | boolean                  |           |          |         | plain   |              | 
Indexes:
    "index_on_measurement_export_2_metering_point_id_consumption" btree (metering_point_id, is_consumption)
Access method: heap

And have following sizes (~50 M rows in the table):

measurements=> \d+
                                List of relations
 Schema |          Name          |   Type   |  Owner   |    Size    | Description 
--------+------------------------+----------+----------+------------+-------------
 public | measurement_export_2   | table    | postgres | 2528 MB    | 

             table             |                              index                              | index_size | index_scans 
-------------------------------+-----------------------------------------------------------------+------------+-------------
 public.measurement_export_2   | index_on_measurement_export_2_metering_point_id_consumption     | 336 MB     |           7

The explain of a simple query looks like this:

EXPLAIN (ANALYZE, BUFFERS) SELECT datetime_to, energy FROM measurement_export_2 WHERE metering_point_id=1267 AND is_consumption;
                                                                                             QUERY PLAN                                                                                              
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using index_on_measurement_export_2_metering_point_id_consumption on measurement_export_2  (cost=0.56..325673.37 rows=92433 width=12) (actual time=2.328..29296.200 rows=121344 loops=1)
   Index Cond: ((metering_point_id = 1267) AND (is_consumption = true))
   Buffers: shared read=74057
   I/O Timings: read=28946.758
 Planning Time: 0.066 ms
 Execution Time: 29317.316 ms
(6 rows)

Things I've tried and checked:

1. Running VACUUM (ANALYZE) measurement_export_2 after index creation and testing few queries before the one above.
2. Changing the index - since "metering_point_id,datetime_to,is_consumption" is unique I tried using the unique index, which in some cases provided extensive improvement but generally only few percent.
3. I checked the RDS metrics: CPU usage is low, balances are OK (burst, IO), there is enough freeable memory - increasing the instance size does not make sense. Unless bringing in additional memory could fit most/all of the data into RAM (somehow). But I do no think this is main issue here.
4. I did increase the "work_mem" setting to "16MB", which helped in one other question, but not here.

What else can I do to improve the query performance?

I am no DBA, just a dev trying to make things work to the best of my ability. Thanks for your help.

Answer 1

If the table is well-VACUUMed and you had an index on all 4 columns, with metering_point_id and is_consumption leading, then you could get an index-only scan which should be fast, as it shouldn't have to jump around the table at all.

Or if you CLUSTERed the table on the index so that the table rows were in the same order as the index entries, then you would be able to read a smaller part of the table and mostly in order, which should be much faster than jumping all around in the table. But CLUSTER is slow, takes a strong lock, and once done it does not stay done as the data turns over in the future.

Or if the table would fit in RAM and would stay there (which depends on how many other large tables there are and how often those ones get used) then the current plan would be fast (once the table found its way into RAM, which pg_prewarm can help with proactively) as it wouldn't need to read the data off from disk.

You are currently reading about 2.5 blocks per millisecond. That is by no means slow, but could be faster if you were willing to pay for it.