Parallel safety of contrib extensions - Mailing list pgsql-general
| From | Winfield, Steven |
|---|---|
| Subject | Parallel safety of contrib extensions |
| Date | |
| Msg-id | DB7PR09MB2537E18FF90C1C1BBF49D628FD830@DB7PR09MB2537.eurprd09.prod.outlook.com Whole thread Raw |
| Responses |
Re: Parallel safety of contrib extensions
(Tom Lane <tgl@sss.pgh.pa.us>)
|
| List | pgsql-general |
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, 1000000) 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.
(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 contents to you. GAM Investments will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice.
pgsql-general by date: