How does Postgres decide if to use additional workers?
0
votes
1
answer
1526
views
I have the following table with an added BTREE-index on "captured_at".
CREATE TABLE datagram
(
id bigserial NOT NULL,
src_re integer NOT NULL,
src_clt integer NOT NULL,
src_meter integer NOT NULL,
captured_at timestamp with time zone NOT NULL,
captured_rssi smallint NOT NULL,
oms_status smallint NOT NULL,
oms_enc bytea,
oms_dec bytea
);
I have the following query:
EXPLAIN (ANALYZE true, BUFFERS true, VERBOSE true)
SELECT
DISTINCT ON ("real_estate"."number", "flat"."number", "meter"."mfct_code", "meter"."reading_serial", "meter"."type") "real_estate"."number" AS "real_estate_nr",
"flat"."number" AS "flat_nr",
"datagram"."id" AS "datagram_id"
FROM "real_estate"
JOIN "flat" ON "real_estate"."id" = "flat"."real_estate"
JOIN "meter_bcd" ON "flat"."id" = "meter_bcd"."flat"
JOIN "meter" ON "meter_bcd"."id" = "meter"."meter_bcd"
JOIN "datagram" ON "datagram"."src_meter" = "meter"."id"
WHERE
(
"real_estate"."id" IN ([...]) AND
"meter"."id" IN ([...]) AND
"datagram"."captured_at" BETWEEN
(
CAST('2020-08-28T10:34:32.855+02:00' AS TIMESTAMP WITH TIME ZONE)
- CAST('P5D' AS INTERVAL)
)
AND
(
CAST('2020-08-28T10:34:32.855+02:00' AS TIMESTAMP WITH TIME ZONE)
+ CAST('P0D' AS INTERVAL)
)
)
ORDER BY
"real_estate"."number" ASC,
"flat"."number" ASC,
"meter"."mfct_code" ASC,
"meter"."reading_serial" ASC,
"meter"."type" ASC,
"datagram"."captured_at" DESC
When that query is applied to the above table with an index on
"captured_at" only, that results in the following query plan. The important
thing to note is that NO parallel workers a re used.
-> Hash Join (cost=246164.35..2004405.07 rows=11323 width=51) (actual time=93.802..5776.755 rows=104607 loops=1)
Hash Cond: (meter.meter_bcd = meter_bcd.id)
-> Hash Join (cost=246019.19..2003889.83 rows=68494 width=37) (actual time=93.067..5744.787 rows=104607 loops=1)
Hash Cond: (datagram.src_meter = meter.id)
-> Index Scan using idx_datagram_captured_at_btree on datagram (cost=0.57..1756571.73 rows=495033 width=20) (actual time=0.054..5451.417 rows=514369 loops=1)
Index Cond: ((captured_at >= ('2020-08-28 10:34:32.855+02'::timestamp with time zone - '5 days'::interval)) AND (captured_at Hash Join (cost=245966.53..272335.67 rows=5419 width=51) (actual time=625.846..1560.103 rows=34869 loops=3)
Hash Cond: (datagram_y2020_h2.src_meter = meter.id)
-> Parallel Append (cost=4.19..25430.72 rows=236911 width=20) (actual time=2.827..863.298 rows=171456 loops=3)
Subplans Removed: 23
-> Parallel Index Scan using datagram_y2020_h2_captured_at_idx on datagram_y2020_h2 (cost=0.44..24051.22 rows=236888 width=20) (actual time=2.826..848.388 rows=171456 loops=3)
Index Cond: ((captured_at >= ('2020-08-28 10:34:32.855+02'::timestamp with time zone - '5 days'::interval)) AND (captured_at Hash Join (cost=264793.42..1666293.23 rows=4332 width=51) (actual time=96.080..638.802 rows=34869 loops=3)
Hash Cond: (oms_rec.meter = meter.id)
-> Nested Loop (cost=1.14..1400747.39 rows=189399 width=20) (actual time=0.145..496.366 rows=171456 loops=3)
-> Hash (cost=264709.53..264709.53 rows=6620 width=39) (actual time=95.521..95.528 rows=40044 loops=3)
Buckets: 65536 (originally 8192) Batches: 1 (originally 1) Memory Usage: 3016kB
-> Parallel Index Scan using idx_clt_rec_captured_at on clt_rec (cost=0.57..14853.95 rows=189399 width=24) (actual time=0.098..81.556 rows=171456 loops=3)
-> Index Scan using pk_oms_rec on oms_rec (cost=0.57..7.32 rows=1 width=12) (actual time=0.002..0.002 rows=1 loops=514369)
-> Hash Join (cost=145.59..264709.53 rows=6620 width=39) (actual time=9.883..86.390 rows=40044 loops=3)
Index Cond: (id = clt_rec.oms_rec)
Index Cond: ((captured_at >= ('2020-08-28 10:34:32.855+02'::timestamp with time zone - '5 days'::interval)) AND (captured_at <= ('2020-08-28 10:34:32.855+02'::timestamp with time zone + '00:00:00'::interval)))
Hash Cond: (meter.meter_bcd = meter_bcd.id)
**So, based on which facts does Postgres decide if to use aadditional
workers or not? Can I see those decisions explained somewhere? I don't
see anything in the query plan. Thanks!**
Asked by Thorsten Schöning
(123 rep)
Feb 9, 2021, 03:51 PM
Last activity: Feb 5, 2025, 09:03 PM
Last activity: Feb 5, 2025, 09:03 PM