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:24:44 |
Message-ID: | CAHkN8V9ffMUTUTxK9sOrZ10H9M9m+LeXZ=tYYTSjAz++f7OK2A@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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 | Michael Lewis | 2019-02-19 21:25:17 | Re: Partial index on JSON column |
Previous Message | Tom Lane | 2019-02-19 21:14:37 | Re: Partial index on JSON column |