2

I have the following table:

                            Table "public.partitioned_11"
     Column     |            Type             | Collation | Nullable |           Default                                                        
----------------+-----------------------------+-----------+----------+----------------------------
 id             | integer                     |           | not null |
 integer_value  | integer                     |           |          |
 invalidated_at | timestamp without time zone |           |          | 
 datetime_value | timestamp without time zone |           |          | 
Partition of: partitioned FOR VALUES IN (11)

and the following two indexes:

"idx1" btree (datetime_value) INCLUDE (integer_value) WHERE partitioned_by = 11 AND invalidated_at IS NULL
"idx2" btree (id) INCLUDE (integer_value) WHERE partitioned_by = 11 AND invalidated_at IS NULL

When I filter for integer_value by id, everything works as expected:

explain select integer_value from partitioned where partitioned_by = 11 and invalidated_at is null and id = 1;
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Index Only Scan using idx2 on partitioned_11 partitioned  (cost=0.43..8.50 rows=4 width=4)
   Index Cond: (id = 1)

But when I do the exact same thing with the timestamp, postgres does not like index scan anymore:

explain select integer_value from partitioned where partitioned_by = 11 and invalidated_at is null and datetime_value = '2020-01-01';
                                                                QUERY PLAN                                                                
------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on partitioned_11 partitioned  (cost=5.68..618.81 rows=161 width=4)
   Recheck Cond: ((datetime_value = '2020-01-01 00:00:00'::timestamp without time zone) AND (partitioned_by = 11) AND (invalidated_at IS NULL))
   ->  Bitmap Index Scan on idx1  (cost=0.00..5.64 rows=161 width=0)
         Index Cond: (datetime_value = '2020-01-01 00:00:00'::timestamp without time zone)

If I do

set enable_bitmapscan = false;

then an index-only scan is done. I am wondering, why is this the case? Is there a universe where bitmap index scan is better than index-only scan? And does it have anything to do with the timestamp datatype itself?

Improve this question
5
  • this might explain your question: stackoverflow.com/questions/58353480/… Commented Mar 29, 2023 at 10:57
  • 1
    Does it change when you VACUUM partitioned_11;? Commented Mar 29, 2023 at 12:40
  • @TZHX unfortunately it does not, it says what bitmap scan, index scan and index-only scan is, however it does not say if there is a world where bitmap scan is better than index-only scan, myself i strongly doubt, yet postgres opted for bitmap index scan over index-only scan in my case Commented Mar 29, 2023 at 13:47
  • @KarelJilek the way I read it, it's useful when postgres thinks it's going to be bringing back a lot of the data based on the predicates. In your first example, postgres only thought 4 rows were coming back, 160ish in second one; in the second, it needed to work out which rows it wanted and decided (based on some internal statistics) that it could more quickly work out which rows it wanted then go and get them, rather than iterate through and return rows as it went. Commented Mar 29, 2023 at 13:52
  • The simplest explanation is that it thinks the bitmap index scan is faster. This is trivial to check, just look at the cost estimate from each plan. That you can't find a blog post mentioning this possibility is not evidence of anything. The universe is not constrained by what you can find in a blog post. Commented Mar 29, 2023 at 14:54
Related questions
4
Postgres slow query (slow index scan)
790
psql: FATAL: role "postgres" does not exist
3
postgres partial index
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.