2

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)

4
  • 1
    You said you use provisioned IOPS. But I don't see that you say the level of the provisioning?
    – jjanes
    Commented Oct 25, 2021 at 19:22
  • 2
    What is your index definition? I can probably guess, but guessing is a poor way to go about things.
    – jjanes
    Commented Oct 25, 2021 at 19:26
  • 1
    If I did the math right, the slow query is reading 628MB from disk at 30MB/s which would take about 20 sec. Get us the table definitions (edit your question) and I’m sure we can improve things.
    – dwhitemv
    Commented Oct 25, 2021 at 19:38
  • @jjanes Thanks yall, added the table and index definitions.
    – bmck
    Commented Oct 25, 2021 at 20:10

1 Answer 1

2
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.

4
  • I believe tacking on amount would basically create row for row in the index, no?
    – bmck
    Commented Oct 25, 2021 at 20:04
  • Maybe. without knowing the definitions of both, I can't know that. But if it does, so what? The index is maintained in the index ordering, while the table is not. So if you can read from just the index that can be a huge benefit. It would be nice if you could do away with the table altogether in that case, but PostgreSQL doesn't offer "index-organized tables" so you can't do that.
    – jjanes
    Commented Oct 25, 2021 at 20:10
  • This resulted in very good results. The uncached version is down to 300ms and the cached version is now 80. Buffers: shared hit=96 read=55182 dirtied=196 I/O Timings: read=155.786
    – bmck
    Commented Oct 25, 2021 at 22:20
  • 1
    On a tangent, a read-only query generating dirtied pages suggests that the underlying table(s) could use more frequent vacuuming and/or have a lot of UPDATE/DELETE activity. Killed index tuples could explain where the dirtied pages are coming from.
    – dwhitemv
    Commented Oct 26, 2021 at 3:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.