Postgres Pro
Downloads
Home   >   mailing lists

Re: Partial index on JSON column - Mailing list pgsql-general

From Samuel Williams
Subject Re: Partial index on JSON column
Date
Msg-id CAHkN8V9dyWdvX0mb3uEE_rhv-y2dGFo7jJm0HLk0hmGGdd81Dw@mail.gmail.com
Whole thread Raw
In response to Re: Partial index on JSON column  (Samuel Williams <space.ship.traveller@gmail.com>)
List pgsql-general
Tree view
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.ship.traveller@gmail.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@sss.pgh.pa.us> wrote:
Samuel Williams <space.ship.traveller@gmail.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

pgsql-general by date:

Previous
From: Michael Lewis
Date:
Subject: Re: Partial index on JSON column
Next
From: Rob Nikander
Date:
Subject: Re: procedures and transactions