CODEWITHSUNDEEP
postgresqlindexing
summercostanza
summercostanza

Reputation: 295

Why postgres doesn't use multiple indexes

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

Upvotes: 1

Views: 290

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247215

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);

Upvotes: 3

Related Questions