0

I am using a boolean inside a jsonb field. What I find out is that when my boolean is a string "true" the index is being used but when i used a bool it is not. I am currently using postgres 13.6 (cloud SQL).

Here is how i am creating the NONE working index:

CREATE INDEX IF NOT EXISTS my_index_name 
ON my_table (field_1, field_2, ((additional_information->>'my_boolean')::bool));

Query:

SELECT *
FROM public.my_table
WHERE my_table.field_1=2644
  AND (my_table.field_2 IS NOT NULL)
  AND (my_table.additional_information->>'my_boolean')::boolean=FALSE

Execution plan:

Seq Scan on my_table (cost=0.00..42024.26 rows=66494 width=8) (actual time=0.169..139.492 rows=2760 loops=1)
  Filter: ((field_2 IS NOT NULL) AND (field_1 = 2644) AND (NOT ((additional_information ->> 'my_boolean'::text))::boolean))
  Rows Removed by Filter: 273753
  Buffers: shared hit=14400 read=22094
Planning Time: 0.464 ms
Execution Time: 139.721 ms

But If I use:

CREATE INDEX IF NOT EXISTS my_index_name_text 
ON my_table (field_1, field_2, (additional_information->>'my_boolean'));

Query:

SELECT *
FROM public.my_table
WHERE my_table.field_1=2644
  AND (my_table.field_2 IS NOT NULL)
  AND (my_table.additional_information->>'my_boolean' = 'true')

Execution plan:

Index Scan using my_index_name_text on my_table (cost=0.42..5343.80 rows=665 width=8) (actual time=0.211..7.123 rows=2760 loops=1)
  Index Cond: ((field_1 = 2644) AND (field_2 IS NOT NULL) AND ((additional_information ->> 'my_boolean'::text) = 'false'::text))
  Buffers: shared hit=3469
Planning Time: 0.112 ms
Execution Time: 7.241 ms

It is working. Does anyone had a similar problem ? Thanks a lot for you help!

xav
  • 5,452
  • 7
  • 48
  • 57
Jonathan Chevalier
  • 993
  • 1
  • 9
  • 18
  • 1
    The index scan is slower than the sequential read... At least for the current data set. – Frank Heikens Jun 22 '22 at 08:21
  • actually had change the boolean value for the index scan. Just updated with the right value. – Jonathan Chevalier Jun 22 '22 at 08:43
  • 1
    Looks like the query planner grossly overextimates the number of rows matching the where condition in the first query for some reason (66494 estimated vs. 2760 actual), which would explain the choice to not use the index if 66494 rows is a sizable part of the table. Have you tried running VACUUM ANALYZE on the table? If that doesn't work you could also try increasing the sample size on the additional_information coulmn via SET STATISTICS – cogitoergosum Jun 22 '22 at 09:40
  • wowww! it worked! – Jonathan Chevalier Jun 22 '22 at 12:10

1 Answers1

0

An index on a piece or text, can't be used for a boolean. You could however cast your index:

CREATE INDEX IF NOT EXISTS my_boolean_index_name 
ON my_table (field_1, field_2, CAST((additional_information->>'my_boolean') AS boolean);
xav
  • 5,452
  • 7
  • 48
  • 57
Frank Heikens
  • 117,544
  • 24
  • 142
  • 135