Trying to understand why a query is filtering when there is a composite index

From: "Stephen Samuel (Sam)" <sam(at)sksamuel(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Trying to understand why a query is filtering when there is a composite index
Date: 2024-08-19 01:55:50
Message-ID: CANRyZ7Z7_JmX7fdLWdZN+ReYiEgafOMB3uva=3JNeQQj+ahnyA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi folks.

I have a table with 4.5m rows per partition (16 partitions) (I know, very
small, probably didn't need to be partitioned).

The table has two columns, a bigint and b text.
There is a unique index on (a,b)
The query is:

SELECT b
FROM table
WHERE a = <id>
AND b IN (<ids>)

The visibility map is almost exclusively true.
This table gets few updates.

The planner says index only scan, but is filtering on b.

Index Only Scan using pkey on table (cost=0.46..29.09 rows=1
width=19) (actual time=0.033..0.053 rows=10 loops=1)
Index Cond: (a = 662028765)
" Filter: (b = ANY
('{634579987:662028765,561730945:662028765,505555183:662028765,472806302:662028765,401361055:662028765,363587258:662028765,346093772:662028765,314369897:662028765,289498328:662028765,217993946:662028765}'::text[]))"
Rows Removed by Filter: 1
Heap Fetches: 11
Planning Time: 0.095 ms
Execution Time: 0.070 ms

My question is, why isn't it using the index for column b? Is this
expected? And why is it doing heap lookups for every row,.

Performance is still good, but I am curious.

Thanks in advance!

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Peter Geoghegan 2024-08-19 01:59:10 Re: Trying to understand why a query is filtering when there is a composite index
Previous Message Marcelo Zabani 2024-08-07 21:10:04 Partition pruning with array-contains check and current_setting function