Thanks for the quick reply Tom,
The reason why I used ::integer for the INDEX is because I assumed it would be more efficient both in space and performance.
In the JSONB field, it is actually an integer, i.e. {"location_age": 1, "suggestion_id": 26}
So, now that I think about it, maybe the way I'm using ::text is wrong.
Any further advice is most appreciated.
Kind regards,
Samuel
On Wed, 20 Feb 2019 at 10:14, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
Samuel Williams <space.ship.traveller@xxxxxxxxx> writes:
> When I do this query:
> 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.
regards, tom lane
