Hi all,
There was a thread about this back in 2016[1], but I've just been bitten by it
and wondered if any (more) extensions, particularly btree_gist, will have their
operators/functions verified and marked as parallel-safe?
In our case, we're interested in the float8 <-> float8 operator. Although this
is defined in btree_gist, we also have plain btree indexes that use this
operator in functional columns. We're currently running v11.7.
An example:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE distance_test AS
SELECT id, mod(id, 10) as m10, mod(id, 17) as m17, random() as r
FROM generate_series(1, 100) as ids(id);
ANALYZE distance_test;
CREATE INDEX idx_distance_test ON distance_test USING btree (m10, m17, (r <->
0.5));
SET force_parallel_mode = on;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;
EXPLAIN ANALYZE
(
SELECT DISTINCT ON (m10, m17) m10, m17
FROM distance_test
WHERE m10 = 5
ORDER BY m10, m17
)
UNION ALL
(
SELECT DISTINCT ON (m10, m17) m10, m17
FROM distance_test
WHERE m10 = 3
ORDER BY m10, m17
)
This gives a nice parallel plan - the two subqueries are farmed out to workers,
each doing a Unique of an Index Only Scan, and the results are
Parallel-Appended.
However, if the two "ORDER BY" clauses have the functional column "r <-> 0.5"
added then parallelism is no longer allowed - the plan becomes a plain Append
of two Subquery Scans.
As an aside, the values of "r <-> 0.5" have been precomputed - which is why an
Index Only Scan can take place - so the supposed parallel-unsafe operator isn't
even being invoked here!
But, either way, the loss of parallelism in my real-world case wasn't easy to
track down, so it would be great if some more of the contrib-but-core-really
extensions, such as btree_gist, could have their functions and operators marked
parallel safe.
And, yes, if I'd used "abs(r - 0.5)" here then parallelism would not have been
lost, but we do use gist indexes elsewhere and we don't want to have to switch
between this and "<->" depending on which index we think a particular query
needs, or worry about subtle differences between them when handling - say -
NaNs.
I can also force parallelism to return if I manually mark the "float8_dist"
function as parallel-safe:
UPDATE pg_proc SET proparallel = 's' WHERE oid = 'float8_dist'::regproc
Looking at the source code[2] I can't see why this would cause issues, but I'm
not an expert - any comments on why this might be an incredibly bad idea would
be most welcome (e.g. will the setting survive a major upgrade of the db, and
an upgrade of the btree_gist extension? Is it in fact subtly NOT parallel safe?)
Best wishes,
Steven.
[1] https://www.postgresql.org/message-id/flat/573E3509.9040309%40proxel.se
[2]
https://github.com/postgres/postgres/blob/master/contrib/btree_gist/btree_float8.c#L102
(Apologies if my company's mail servers append an annoying disclaimer here...)
** Cantab Capital Partners LLP is now named GAM Systematic LLP. Please note
that our email addresses have changed from @cantabcapital.com to @gam.com.**
This email was sent by and on behalf of GAM Investments. GAM Investments is the
corporate brand for GAM Holding AG and its direct and indirect subsidiaries.
These companies may be referred to as 'GAM' or 'GAM Investments'. In the United
Kingdom, the business of GAM Investments is conducted by GAM (U.K.) Limited
(No. 01664573) or one or more entities under the control of GAM (U.K.) Limited,
including the following entities authorised and regulated by the Financial
Conduct Authority: GAM International Management Limited (No. 01802911), GAM
London Limited (No. 00874802), GAM Sterling Management Limited (No. 01750352),
GAM Unit Trust Management Company Limited (No. 2873560) and GAM Systematic LLP
(No. OC317557). GAM (U.K.) Limited and its regulated entities are registered in
England and Wales. The registered office and principal place of business of GAM
(U.K.) Limited and its regulated entities is at 8 Finsbury Circus, London,
England, EC2M 7GB. The registered office of GAM Systematic LLP is at City
House, Hills Road, Cambridge, CB2 1RE. This email, and any attachments, is
confidential and may be privileged or otherwise protected from disclosure. It
is intended solely for the stated addressee(s) and access to it by any other
person is unauthorised. If you are not the intended recipient, you must not
disclose, copy, circulate or in any other way use or rely on the information
contained herein. If you have received this email in error, please inform us
immediately and delete all copies of it. See -
https://www.gam.com/en/legal/email-disclosures-eu/ for further information on
confidentiality, the risks of non-secure electronic communication, and certain
disclosures which we are required to make in accordance with applicable
legislation and regulations. If you cannot access this link, please notify us
by reply message and we will send the
