PostgreSQL RDS: Slower than expected performance when large table / index is not in cache

Question

I have a fairly large table, 43GB and a 14GB index, that consists of time series cost data. I am querying by date and summing the amount. When this data is not in the cache (either OS or Postgres) the query can take up to 50 seconds to run for users that have millions of rows of data for that specific time period, but usually filtered down to thousands by the other filters being applied. As far as I can tell the index is well optimized based on the query patterns. I have run EXPLAIN (ANALYZE, BUFFERS) and can clearly see that reading from disk is the slow down.

My workload is a bit odd because I am doing large batch writes and large batch deletes so I believe the VACUUM job is doing a lot of work. I have not tuned this at all, but I actually don't think that would help based on the index I am using. I am tracking the "active imports" and then removing the old inactive ones. The active import id is included in the query and the index so I shouldn't be scanning dead tuples from previous imports.

I have also tried tuning random_page_cost down to 1.1 and it will use an Index Scan instead of a Bitmap Heap Scan, but the performance ends up about the same.

I am running Postgres 13.4 on RDS db.r6g.2xlarge (8vCPUs, 64GB of RAM) and provisioned IOPS (11,000 - I usually max out at 9,000 total read+write).

I would not expect the non-cached query to be so slow. Do I have the wrong expectation here? I have already tuned shared_buffers to 40% and realize that based on the size of the table and index I should probably jump up to the 128G version, which will be my next step.

                                                        Table 

"public.service_costs"
         Column          |              Type              | Collation | Nullable |         Default          | Storage  | Stats target | Description
-------------------------+--------------------------------+-----------+----------+--------------------------+----------+--------------+-------------
 id                      | uuid                           |           | not null | public.gen_random_uuid() | plain    |              |
 date                    | timestamp without time zone    |           | not null |                          | plain    |              |
 cost_type               | character varying              |           | not null |                          | extended |              |
 service                 | character varying              |           | not null |                          | extended |              |
 amount                  | numeric                        |           | not null |                          | main     |              |
 cost_category           | character varying              |           |          |                          | extended |              |
 cost_sub_category       | character varying              |           |          |                          | extended |              |
 service_costs_import_id | bigint                         |           | not null |                          | plain    |              |

Indexes:
    "service_costs_pkey" PRIMARY KEY, btree (id)
    "indx_srvc_csts_on_cst_type__dt__srvc__cst_ctgry__cst_sb_ctgry" btree (service_costs_import_id, cost_type, date, service, cost_category, cost_sub_category)
Access method: heap

...

    schema_name     |                             relname                             |    size    | table_size
--------------------+-----------------------------------------------------------------+------------+-------------
 public             | service_costs                                                   | 43 GB      | 46511259648
 public             | indx_srvc_csts_on_cst_type__dt__srvc__cst_ctgry__cst_sb_ctgry   | 14 GB      | 15080833024

...

EXPLAIN (ANALYZE,BUFFERS) SELECT SUM ( service_costs . amount )
FROM service_costs WHERE service_costs . cost_type IN (...)
AND service_costs . service_costs_import_id IN (2066, 2067, 1267, 1269, 1268, 1270, 2068, 1273, 4996, 5047)
AND service_costs.service = '....'
AND "service_costs"."date" BETWEEN '2021-10-01' AND '2021-10-31 23:59:59.999999';

...

 Aggregate  (cost=1390974.93..1390974.94 rows=1 width=32) (actual time=17067.830..17067.831 rows=1 loops=1)
   Buffers: shared hit=6854 read=80448 dirtied=754
   I/O Timings: read=16236.006
   ->  Bitmap Heap Scan on service_costs  (cost=351286.12..1390173.71 rows=320487 width=5) (actual time=4827.074..16996.060 rows=323382 loops=1)
         Recheck Cond: ((service_costs_import_id = ANY ('{2066,2067,1267,1269,1268,1270,2068,1273,4996,5047}'::bigint[])) AND ((cost_type)::text = ANY ('{...}'::text[])) AND (date >= '2021-10-01 00:00:00'::timestamp without time zone) AND (date <= '2021-10-31 23:59:59.999999'::timestamp without time zone) AND ((service)::text = '...'::text))
         Heap Blocks: exact=70327
         Buffers: shared hit=6854 read=80448 dirtied=754
         I/O Timings: read=16236.006
         ->  Bitmap Index Scan on indx_srvc_csts_on_cst_type__dt__srvc__cst_ctgry__cst_sb_ctgry  (cost=0.00..351206.00 rows=320487 width=0) (actual time=4815.759..4815.759 rows=323382 loops=1)
               Index Cond: ((service_costs_import_id = ANY ('{2066,2067,1267,1269,1268,1270,2068,1273,4996,5047}'::bigint[])) AND ((cost_type)::text = ANY ('{...}'::text[])) AND (date >= '2021-10-01 00:00:00'::timestamp without time zone) AND (date <= '2021-10-31 23:59:59.999999'::timestamp without time zone) AND ((service)::text = '...'::text))
               Buffers: shared hit=159 read=16816
               I/O Timings: read=4575.310
 Planning Time: 0.159 ms
 Execution Time: 17067.865 ms
(14 rows)

...

Aggregate  (cost=1390974.93..1390974.94 rows=1 width=32) (actual time=403.002..403.003 rows=1 loops=1)
   Buffers: shared hit=87302
   ->  Bitmap Heap Scan on service_costs  (cost=351286.12..1390173.71 rows=320487 width=5) (actual time=206.128..338.491 rows=323382 loops=1)
         Recheck Cond: ((service_costs_import_id = ANY ('{2066,2067,1267,1269,1268,1270,2068,1273,4996,5047}'::bigint[])) AND ((cost_type)::text = ANY ('{....}'::text[])) AND (date >= '2021-10-01 00:00:00'::timestamp without time zone) AND (date <= '2021-10-31 23:59:59.999999'::timestamp without time zone) AND ((service)::text = '...'::text))
         Heap Blocks: exact=70327
         Buffers: shared hit=87302
         ->  Bitmap Index Scan on indx_srvc_csts_on_cst_type__dt__srvc__cst_ctgry__cst_sb_ctgry  (cost=0.00..351206.00 rows=320487 width=0) (actual time=195.167..195.167 rows=323382 loops=1)
               Index Cond: ((service_costs_import_id = ANY ('{...}'::bigint[])) AND ((cost_type)::text = ANY ('{...}'::text[])) AND (date >= '2021-10-01 00:00:00'::timestamp without time zone) AND (date <= '2021-10-31 23:59:59.999999'::timestamp without time zone) AND ((service)::text = '....'::text))
               Buffers: shared hit=16975
 Planning Time: 0.168 ms
 Execution Time: 403.042 ms

(11 rows)

Answer 1

Index Cond: ((service_costs_import_id = ANY ('{2066,2067,1267,1269,1268,1270,2068,1273,4996,5047}'::bigint[])) AND ((cost_type)::text = ANY ('{...}'::text[])) AND (date >= '2021-10-01 00:00:00'::timestamp without time zone) AND (date <= '2021-10-31 23:59:59.999999'::timestamp without time zone) AND ((service)::text = '...'::text))

It looks like your index is on (service_costs_import_id, cost_type, date, service), although it is possible it has more columns than that which are just not used in the query. If those are the columns in order, then a problem with that index is that "service" cannot be used efficiently, as it follows the "date" column which is used in a range rather for equality. So "service" can only be used to filter out rows, not to jump to a specific place in the index. If you reverse the order of the last two columns in the index, then it would be able to make efficient use of all the columns.

Better yet, if you reverse the order and then tack "amount" onto the end, you could get an index-only scan. But doing that effectively might need a higher level of vacuuming.

With 11,000 IOPS provisioned, it should not take over 16000 ms to read 80000 blocks. But that ignores latency, which I don't find described in anything but the vaguest of terms in AWS docs. If you need to wait for one block to come back before sending the next request you will not likely achieve the highest available IOPS. You could crank up effective_io_concurrency to see if that can improve things by having multiple requests outstanding at a time. (It will not improve the bitmap index scan part, just the bitmap heap scan.) And of course this analysis depends on there being only one query at a time. If multiple queries have to share throughput they have to divide it.