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 ->> 'suggestion_id'::text) IS NOT NULL)
When I do this query:
EXPLAIN SELECT COUNT(*) FROM "user_event" WHERE ((parameters ->> 'suggestion_id'::text)::integer = 26) AND what = 'suggestion_notification';
Aggregate (cost=7115959.48..7115959.49 rows=1 width=8)
-> Bitmap Heap Scan on user_event (cost=37360.24..7115907.56 rows=20771 width=0)
Recheck Cond: ((what)::text = 'suggestion_notification'::text)
Filter: (((parameters ->> 'suggestion_id'::text))::integer = 26)
-> Bitmap Index Scan on index_user_event_for_clustering (cost=0.00..37355.05 rows=4154273 width=0)
Index Cond: ((what)::text = 'suggestion_notification'::text)
It's slow. I need to explicitly add the NULL constraint:
EXPLAIN SELECT COUNT(*) FROM "user_event" WHERE ((parameters ->> 'suggestion_id'::text)::integer = 26) AND ((parameters ->> 'suggestion_id'::text) IS NOT NULL) AND what = 'suggestion_notification';
Aggregate (cost=38871.48..38871.49 rows=1 width=8)
-> Index Scan using index_user_event_for_suggestion_notification on user_event (cost=0.42..38819.81 rows=20668 width=0)
Index Cond: ((((parameters ->> 'suggestion_id'::text))::integer = 26) AND ((what)::text = 'suggestion_notification'::text))
I feel like the null constraint should be implicit.
That being said:
- Is my partial index wrong? Should I write it differently so the optimiser knows this?
- Is my query wrong? How can I make the most use of this index without being explicit?
- Any other suggestions for how I can do this?
Thanks
Samuel