From: | Samuel Williams <space(dot)ship(dot)traveller(at)gmail(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Partial index on JSON column |
Date: | 2019-02-19 20:41:40 |
Message-ID: | CAHkN8V9Rfh6uAjQLURJfnHsQfC_MYiFUSWEVcwVSiPdokmkniw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Reid Thompson | 2019-02-19 20:51:16 | PG 9.6 managed fail over of master, how do i manage a pg_logical subscriber |
Previous Message | Adrian Klaver | 2019-02-19 20:41:03 | Re: procedures and transactions |