Sample Header Ad - 728x90

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&#246;ning (123 rep)
Feb 9, 2021, 03:51 PM
Last activity: Feb 5, 2025, 09:03 PM