From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | Samuel Williams <space(dot)ship(dot)traveller(at)gmail(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Partial index on JSON column |
Date: | 2019-02-19 21:28:41 |
Message-ID: | CAHOFxGr7F3DVJkYPHOmNPpEYD==DvyYpzM8JTA2vrLrnQSRnNg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
> On Wed, 20 Feb 2019 at 10:14, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Samuel Williams <space(dot)ship(dot)traveller(at)gmail(dot)com> 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.
>>
>
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 )
RETURNS BOOL AS
$BODY$
BEGIN
RETURN pInput::BOOL;
EXCEPTION WHEN OTHERS THEN
RETURN FALSE;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
This is probably not best practice however.
>
>> regards, tom lane
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Samuel Williams | 2019-02-19 21:29:42 | Re: Partial index on JSON column |
Previous Message | Michael Lewis | 2019-02-19 21:25:17 | Re: Partial index on JSON column |