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."