I need to be able to quickly (1-2 seconds) retrieve pages of 50,000 records from table that contains ~3,000,000 records. There's a UNIQUE index on a string primary key which contains values like '57133c4aea332cb17b001a28'.

string_id CHARACTER VARYING(255)

CREATE UNIQUE INDEX index_name ON users USING BTREE (string_id);

I've tried

FROM users
WHERE string_id > '57133c4aea332cb17b001a28'
ORDER BY string_id
LIMIT 50000

but it has a very poor execution time:

Limit  (cost=0.11..48044.67 rows=50000 width=29)
  ->  Index Scan using index_name on users  (cost=0.11..2799915.87 rows=2913874 width=29)
    Index Cond: ((string_id)::text > '57133c4aea332cb17b001a28'::text)

Limit  (cost=0.11..48237.53 rows=50000 width=29) (actual time=0.561..25740.665 rows=50000 loops=1)
  ->  Index Scan using index_name on users  (cost=0.11..2792079.10 rows=2894101 width=29) (actual time=0.560..25730.852 rows=50000 loops=1)
    Index Cond: ((string_id)::text > '57133c4aea332cb17b001a28'::text)
Planning time: 0.364 ms
Execution time: 25747.091 ms

I'm using PostgreSQL 9.5.

Why is it so hard for DB to get 50,000 records using ordered index and what can I do to improve it?

  • 1
    Show us the CREATE TABLE, the indexes on the table and which Postgres version you use. – ypercubeᵀᴹ Nov 10 '16 at 11:58
  • Updated post with relevant information. I'm not sure the rest of table definition or other indexes have anything to do with this issue. – Murad Yusufov Nov 10 '16 at 15:32
  • Can you compare with the same query immediately after a REINDEX of the index on which the index scan occurs? The idea is to establish how much the disk cache state and the index bloat contribute to these 25s. – Daniel Vérité Nov 10 '16 at 16:15
  • @a_horse_with_no_name as you can see, I have already applied techniques from the link you've provided and they don't help. Cost for index scan is too high. – Murad Yusufov Nov 11 '16 at 5:18

If the 50,000 records are scattered all over the place, and it takes 4ms to fetch each one (15,000 rpm hard drive), it would take 200 seconds. So 25 seconds is reasonable, since it is unlikely they will be scattered completely at random.

You can CLUSTER users on index_name which will reorder the table to be in the same order as the string_id. But it won't automatically stay clustered as you do updates and more insertions.

You can add an index on (string_id, customer_id) and see if an index-only scan will solve your problem.

| improve this answer | |
  • I'm not sure if clustering is such a good idea because a lot of rows in this table are updated every day and there are a lot of other queries against different columns. This index does not solve the problem. However, I do have a customer_id, string_id index. Changing ORDER BY clause to customer_id, string_id makes planner use index-only scan which takes ~2 seconds at worst. – Murad Yusufov Nov 11 '16 at 7:00

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