From: | Marcos Pegoraro <marcos(at)f10(dot)com(dot)br> |
---|---|
To: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | massive update on gin index |
Date: | 2022-09-14 19:33:47 |
Message-ID: | CAB-JLwa1KMTj17y5cCdNQC_KFd16VXViZ_Hf1Jt+AUgiUo75Qg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
In a table with people's info I have 3 phone numbers, mobile, work and
home. But then some have 2 mobiles, some have 2 work numbers, so decided to
test it as an array of json. I know I could have another table for that,
but I was just testing.
So my original table had
Mobile, Work, Home and all of them are btree indexed.
Then added a jsonb field and updated it with those 3 phone numbers on it
[{"phone": 2236279878, "type": 1}, {"phone": 22998432631, "type": 2}]
[{"phone": 22996783278, "type": 2}]
create index idxPhones on People using gin(Phones)
If I select using old or new fields, both uses index and Execution Time is
similar
explain analyze select * from People where Phones @>
'[{"phone": 2236279878}]';
explain analyze select * from People where Mobile = 2236279878 or Work
= 2236279878 or Home = 2236279878;
But then I repeated 2 or 3 times that update which stores those 3 phones on
json and then my gin index became slow, very very slow, why ?
select using btree on 3 phone numbers - Execution Time: 0.164 ms
select using gin on json on first update - Execution Time: 0.220 ms
select using gin on json next to 2 or 3 updates - Execution Time: 11.220 ms
And that execution time will come back to 0.220 ms only if I recreate the
index.
Then I found gin_pending_list_limit and fast_update which I think are used
to update GIN indexes, but didn´t find any examples of both.
What am I missing ? That gin index needs to have some more options or
attributes on it ?
I know in a day by day use I'll never do that massive update twice but just
to understand when will this index be updated ?
Thanks
Marcos
From | Date | Subject | |
---|---|---|---|
Next Message | Guyren Howe | 2022-09-14 19:38:17 | Re: massive update on gin index |
Previous Message | Laurenz Albe | 2022-09-14 16:58:30 | Re: CVE-2022-2625 |