> EXPLAIN SELECT COUNT(*) FROM "user_event" WHERE ((parameters ->> > 'suggestion_id'::text)::integer = 26) AND what = 'suggestion_notification';
> It's slow. I need to explicitly add the NULL constraint:
Try it like
EXPLAIN SELECT COUNT(*) FROM "user_event" WHERE ((parameters ->> 'suggestion_id'::text) = '26') AND what = 'suggestion_notification';
I don't think we assume that CoerceViaIO is strict, and without that the deduction that the value couldn't be null doesn't hold. In any case you're better off without the runtime type conversion: that isn't doing much for you except raising the odds of getting an error.
For run-time safe comparisons of something stored in jsonb that I expect to only have booleans (and I don't trust input to always be valid), I have a utility function like this-
CREATE OR REPLACE FUNCTION public.util_to_bool( pInput TEXT )