Why postgres doesn't use multiple indexes

Question

We have a table schema like below

user_id UUID
tenant_id UUID
content_id UUID
created_at timestamp

There are close to 12 million records as of now. when running a query like below:

SELECT * FROM content
    WHERE tenant_id = 'uuid1'
    AND user_id IN ('uuid2')
ORDER BY created_at DESC
LIMIT 100 OFFSET 0;

it takes some times close to a minute.

There are indexes on tenant_user_filter_index(tenant_id, user_id) and (created_at desc). I ran explain analyze, either it used the created_at_idx and filter out results or sometimes it using the tenant_user_filter_index and then doing top N heap sort.

Sample explain analyze:

Limit  (cost=561.88..562.13 rows=100 width=24) (actual time=21967.512..21967.529 rows=100 loops=1)
  Buffers: shared hit=258 read=1265
  I/O Timings: read=21946.089
  ->  Sort  (cost=561.88..562.22 rows=136 width=24) (actual time=21967.510..21967.519 rows=100 loops=1)
        Sort Key: created_at DESC
        Sort Method: top-N heapsort  Memory: 32kB
        Buffers: shared hit=258 read=1265
        I/O Timings: read=21946.089
        ->  Index Scan using tenant_user_filter_index on content  (cost=0.56..557.06 rows=136 width=24) (actual time=69.727..21962.150 rows=27666 loops=1)
"              Index Cond: ((tenant_id = 'uuid1'::uuid) AND (user_id = 'uuid2'::uuid)))"
              Buffers: shared hit=255 read=1265
              I/O Timings: read=21946.089
Planning Time: 0.573 ms
Execution Time: 21967.559 ms

I ran analyze command also to update query planner.

Please let me know what could be possible issues to look into

Does it help to replace user_id IN ('uuid2') with user_id = 'uuid2'? — fafl
– fafl, Commented Oct 10, 2022 at 8:36
It's the index scan that takes a lot of time, not the sort. The sort takes just 5 milliseconds, there is not much to gain over there. What you could do, is to include created_at in the index tenant_user_filter_index as well. Then the query could stop when it has 100 records, instead of collecting all 27666 rows. — Frank Heikens
– Frank Heikens, Commented Oct 10, 2022 at 8:38
@FrankHeikens yeah, will be trying that. Also noticing when testing the query on replica, it takes much lesser. Probably could be wrt number of connections handled already. — summercostanza
– summercostanza, Commented Oct 10, 2022 at 9:13

Laurenz Albe · Accepted Answer · 2022-10-10 08:39:13Z

3

There is no way to combine two indexes where one is used for the WHERE clause and the other for ORDER BY. That's not a shortcoming, it lies in the nature of things: rows returned by the first index are the correct ones, but in the wrong order and vice versa.

If you want both the WHERE condition and ORDER BY supported by an index, you have to create a single index that can do it all:

CREATE INDEX ON content (tenant_id, user_id, created_at);

answered Oct 10, 2022 at 8:39

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why postgres doesn't use multiple indexes

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related