What SQL execution time should I expect when querying 4k records in a 150M records table?

Question

We currently have performance issues with a SQL query on RDS Postgres 11.8. Here is the query:

SELECT id FROM answer_submission WHERE student_id = 3983137;

The table "answer_submission" has about 150M records. The query returns about 4k ids.

The query takes ~2s to execute which is problematic for the user experience in our app. (Note that this query is for debug purpose. The real query select most of the columns and thus adding all these columns in the index would make the index heavy)

An analysis of the query confirms that the B-TREE index that we created is correctly used:

Index Scan using answer_submission_student_id on public.answer_submission (cost=0.57..2505.35 rows=842 width=16) (actual time=2.356..2346.005 rows=4604 loops=1)
Output: id
Index Cond: (answer_submission.student_id = 3983137)
Buffers: shared hit=3 read=4392 written=1118
I/O Timings: read=2298.117 write=16.805
Planning Time: 2.370 ms
Execution Time: 2349.484 ms

I understand from this analysis that the bottleneck is the time to read on the disk.

I don't have a critical opinion about this response time. Is it in the range of time that one should expect for querying 4k records in a 150M records table? Or is there an issue somewhere? Do you have any clues on how to improve the execution time?

I already read this question Slow index scans in large table but before implementing this kind of solutions I would like to make sure I can't fix the root cause of this long execution time.

Our server has 64GB of RAM. The size of the index is ~4Gb and we have other tables with similar index sizes.

This behavior is not an occasional behavior. It can be consistently reproduced at any time. Also there are many records saved at any time in this table.

Thanks for you help

Answer 1

It looks like you were measuring the query right after importing a lot of data into the table, so your query has to set hint bits, or like shared_buffers is so small that the query finds no free buffers and has to write out some (written=1118).

Try an index that includes id so you can get an index only scan and VACUUM the table:

CREATE INDEX ON answer_submission (student_id) INCLUDE (id);
VACUUM answer_submission;

That should make the query fast.

Answer 2

The total size of your tables and indexes far exceeds your RAM, so unless this one table is the only heavily used one you won't be able to get it stay cached.

You could cluster the table on the index being used, which would rearrange the table data so that it would be faster to read all the data with the same student_id. But it won't stay clustered in the face of future changes (which you seem to have a lot of) and clustering is a heavy operation which hold a lock on the table for a long time, and of course it can only be clustered on one index at any time. So this might not be a very general solution.

You could also partition on student_id, probably using hash partitioning. This wouldn't be as good as a freshly clustered table, but it maintains itself in the face of future changes.

Also, a faster IO subsystem couldn't hurt. Setting effective_io_concurrency to a large value might also help, but it would only work for bitmap scans, which this does not seem to be (but I'm not sure it wouldn't use bitmap scan--it seems like a good candidate for one.)