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!
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 |