From: | Samuel Williams <space(dot)ship(dot)traveller(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Partial index on JSON column |
Date: | 2019-02-19 21:29:42 |
Message-ID: | CAHkN8V9dyWdvX0mb3uEE_rhv-y2dGFo7jJm0HLk0hmGGdd81Dw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
So, I checked my original code, and I didn't add `::text`, it was actually
added by Postgres:
EXPLAIN for: SELECT "user_event".* FROM "user_event" WHERE
"user_event"."what" = $1 AND (((parameters ->> 'suggestion_id')::integer) =
119 AND ((parameters ->> 'suggestion_id') IS NOT NULL)) [["what",
"suggestion_notification"]]
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Index Scan using index_user_event_for_suggestion_notification on
user_event (cost=0.42..77193.95 rows=20669 width=138)
Index Cond: ((((parameters ->> 'suggestion_id'::text))::integer = 119)
AND ((what)::text = 'suggestion_notification'::text))
(2 rows)
Is there some way to directly use the integer value in the index with
minimal type coercions?
Thanks
Samuel
On Wed, 20 Feb 2019 at 10:24, Samuel Williams <
space(dot)ship(dot)traveller(at)gmail(dot)com> wrote:
> Thanks for the quick reply Tom,
>
> I will try your advice.
>
> 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(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.
>>
>> regards, tom lane
>>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Nikander | 2019-02-19 21:48:00 | Re: procedures and transactions |
Previous Message | Michael Lewis | 2019-02-19 21:28:41 | Re: Partial index on JSON column |