Обсуждение: Build or update inarray GIST index very slow.
Build or update inarray GIST index very very slow.
test=# select version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 10.3 on x86_64-apple-darwin17.3.0, compiled by Apple LLVM version 9.0.0 (clang-900.0.39.2), 64-bit
(1 row)
test=#
test=# \timing
Timing is on.
test=# CREATE TABLE products (
test(# id SERIAL,
test(# CONSTRAINT products_pkey PRIMARY KEY(id),
test(# filters INTEGER[] DEFAULT ARRAY[]::integer[] NOT NULL
test(# );
CREATE TABLE
Time: 7,218 ms
test=# INSERT INTO products (filters)
test-# SELECT distinct array[
test-# (random() * 99999999)::integer,
test-# (random() * 99999999)::integer,
test-# (random() * 99999999)::integer,
test-# (random() * 99999999)::integer
test-# ]
test-# FROM generate_series(1, 1000000) AS x(id);
INSERT 0 1000000
Time: 5802,598 ms (00:05,803)
test=# CREATE EXTENSION intarray;
CREATE EXTENSION
Time: 51,565 ms
test=# CREATE INDEX ON public.products
test-# USING gin (filters public.gin__int_ops);
CREATE INDEX
Time: 18428,038 ms (00:18,428)
test=# CREATE INDEX ON products
test-# USING gist (filters gist__intbig_ops);
CREATE INDEX
Time: 62820,837 ms (01:02,821)
test=# CREATE INDEX ON products
test-# USING gist (filters gist__int_ops);
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
^CCancel request sent
kill process
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
Time: 256303,399 ms (04:16,303)
!>
!> \q
Create index newer finish, I waited 12 hours.
But in documentation "As a rule of thumb, a GIN index is faster to search than a GiST index, but slower to build or update; so GIN is better suited for static data and GiST for often-updated data."
On Fri, Mar 9, 2018 at 1:53 PM, Valery Komarov <komarov@valerka.net> wrote:
Also, note that that sentence was removed from the documentation in 9.5 onward (commit 8e1ad1b37c7afdf99). I can't find the mailing-list thread discussing it, but I think that that advice was deemed too vague to be useful.Build or update inarray GIST index very very slow.
Yes, it is. I think there is room for improvement for the algorithm for picking the pivot.
But in documentation "As a rule of thumb, a GIN index is faster to search than a GiST index, but slower to build or update; so GIN is better suited for static data and GiST for often-updated data."
Rules of thumb are not always true, that is what makes them rules of thumb.
Cheers,
Jeff