1) as you already have discovered, you can't use b-tree as the index size is bigger than the page size
2) given:
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.
You would have to use GIN. And no, GIN doesn't use hash functions nor a brute-force algorithm. It's a reverse index:
A GIN index stores a set of (key, posting list) pairs, where a posting list is a set of row IDs in which the key occurs. The same row ID can appear in multiple posting lists, since an item can contain more than one key. Each key value is stored only once, so a GIN index is very compact for cases where the same key appears many times.
Internally, a GIN index contains a B-tree index constructed over keys, where each key is an element of one or more indexed items (a member of an array, for example)
This would do what you desire:
WITH p AS (
INSERT INTO parent_table (column_1)
SELECT $1
RETURNING id)
INSERT INTO child_table (parent_table_id, column_a, column_b)
SELECT p.id, t.a, t.b
FROM p, (SELECT unnest($2::text[]) AS a, unnest($3::bigint[]) AS b) t
The subtle difference here is that unnest()
calls in the same SELECT
list are unnested in parallel if the number of elements is identical. Careful though: In Postgres 9.6 or older, if the number is not the same, it results in a Cartesian product instead. The behavior was sanitized in Postgres 10. See:
You could use a cleaner form with generate_subscripts()
or other techniques, but those would be much more verbose. Details in this related question:
Postgres 9.4
The new WITH ORDINALITY
in Postgres 9.4 allows a much cleaner (and only moderately more verbose) form for this:
WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, ta.a, tb.b
FROM p
, unnest($2::text[]) WITH ORDINALITY AS ta(a, rn)
JOIN unnest($3::bigint[]) WITH ORDINALITY AS tb(b, rn) USING (rn);
And this special case can be even simpler with the new form of unnest()
that accepts multiple arrays:
WITH p AS (...)
INSERT INTO child_table (...)
SELECT p.id, t.a, t.b
FROM p, unnest($2::text[], $3::bigint[]) AS t(a, b);
Example in this related answer.
Best Answer
While I think Postgresql has had arrays since before SQL-99, the SQL-99 standard specifies 1-indexed arrays, and it's consistent with other areas in SQL where the first element is 1 instead of 0.
PostgreSQL actually has support for custom array start indexes, but I strongly advise you not to use it.