Thanks Tom, I did solve the problem by adding the null constraint for now,
it's a quick solution, and I look forward to the future where this case is
handled appropriately.
On Wed, 20 Feb 2019 at 12:17, Tom Lane wrote:
> I wrote:
> > Try it like
>
> > EXPLAIN SELECT COUNT(*) FROM "user_event" WH
I wrote:
> 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.
Hm
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",
"su
>
> On Wed, 20 Feb 2019 at 10:14, Tom Lane wrote:
>
>> Samuel Williams 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 expli
On Tue, Feb 19, 2019 at 1:41 PM Samuel Williams <
space.ship.travel...@gmail.com> wrote:
> Hello
>
> I have a table with ~3 billion events.
>
> Of this, there are a small subset of events which match the following
> query:
>
> CREATE INDEX index_user_event_for_suggestion_notification ON
> public.u
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
Samuel Williams 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 "
Hello
I have a table with ~3 billion events.
Of this, there are a small subset of events which match the following query:
CREATE INDEX index_user_event_for_suggestion_notification ON
public.user_event USING btree parameters ->>
'suggestion_id'::text))::integer), what) WHERE ((parameters ->>
