0

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 <= ('2020-08-28 10:34:32.855+02'::timestamp with time zone + '00:00:00'::interval)))

For various reasons I tested the above table as a partitioned one as well, with individual partitions containing the rows of one year only. The important thing to note is that I simply kept the same index on "captured_at" like before, though the query plan looks different now:

Workers Planned: 2
Workers Launched: 2
->  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 <= ('2020-08-28 10:34:32.855+02'::timestamp with time zone + '00:00:00'::interval)))

It seems that only because of a different number of rows per individual table, additional workers are used. Though, in the past I had all of those "captured_at" in one table as well, only with far less columns than currently and for that table additional workers have been used as well, pretty much like is the case now:

Workers Planned: 2
Workers Launched: 2
->  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!

Improve this question
5
  • 1
    stackoverflow.com/a/56882885
    – user1822
    Feb 9, 2021 at 15:55
  • log3(table size / min_parallel_table_scan_size) What is table size there, really the amount of storage used or the number of rows? The former is different in my case, the latter the same.
    – Thorsten Schöning
    Feb 9, 2021 at 15:58
  • I would assume pg_class.relpages * 8192
    – user1822
    Feb 9, 2021 at 16:00
  • ALTER TABLE datagram SET (parallel_workers = 20); seems to be ignored, even though in your linked answer "force" is mentioned and at least 2 workers should be possible when looking at the other plans.
    – Thorsten Schöning
    Feb 9, 2021 at 16:07
  • 1
    The parallel_workers table setting only affects how many workers are started for parallel execution plans; it does not affect which plan is picked, or non-parallel plans at all, which is why it appears to be ignored for the table that isn't generating parallel plans. (I don't have any insight on the actual question, I'm afraid.)
    – AdamKG
    Feb 9, 2021 at 21:21

1 Answer 1

Reset to default
0

Because the addition between timestamp with time zone and interval is STABLE, not IMMUTABLE, the optimizer won't perform the addition and won't treat the result as a constant. Therefore, PostgreSQL won't perform partition pruning at query planning time. The Subplans Removed: 23 shows that you are running PostgreSQL v11 or better, and the executor skips scanning the other partitions at run time.

Since the optimizer plans to scan all partitions, it decides on the number of parallel workers based on a hard-coded heuristic in src/backend/optimizer/path/allpaths.c:

/*
 * If the use of parallel append is permitted, always request at least
 * log2(# of children) workers.  We assume it can be useful to have
 * extra workers in this case because they will be spread out across
 * the children.  The precise formula is just a guess, but we don't
 * want to end up with a radically different answer for a table with N
 * partitions vs. an unpartitioned table with the same data, so the
 * use of some kind of log-scaling here seems to make some sense.
 */
if (enable_parallel_append)
{
    parallel_workers = Max(parallel_workers,
                           fls(list_length(live_childrels)));
    parallel_workers = Min(parallel_workers,
                           max_parallel_workers_per_gather);
}

fls(n) actually calculates log2(n) + 1.

There is no way to influence that decision, except that you can set max_parallel_workers_per_gather to limit the number of workers chosen (which happened in your case). You cannot set storage parameters on a partitioned table, and the setting of parallel_workers on the individual partitions is not used here.

This number is too high in your case, because it is based on 24 partitions rather than a single one. There is certainly room for improvement here.

Improve this answer
6
  • Am using Postgres 11, not 12, subplans are removed anyway. Don't get the "This number is too high": I do have parallel workers in one old table and with the new partitioned one, but e.g. NOT when the same table is NOT partitioned anymore. So which number is to high in case of partitions vs. not? Which improvement are you mentioning, less partitions even though I have parallel index scans like I want to?
    – Thorsten Schöning
    Feb 9, 2021 at 16:59
  • Actually, I think my answer is to the point. Except that change was in v11, not in v12. You wonder why you have two parallel workers? My answer explains it, doesn't it?
    – Laurenz Albe
    Feb 10, 2021 at 12:00
  • I wonder why I sometimes get two workers and sometimes not. One new large table: No workers. One old large table: 2 workers. One partitioned new table: Sometimes 2 workers, sometimes none, depends on applied index. Though, it's always the same data with the same amount and same overall rows. Why aren't workers used especially always for large datasets like my new one table? It's done for the old table with less columns, but e.g. the same number of rows.
    – Thorsten Schöning
    Feb 10, 2021 at 13:58
  • In the end, I have EXACTLY the use case in your source code comment: The same number of rows and amount of data in one unpartitioned vs. one partitioned table leads to a totally different number of workers. That is a radically different answer in my opinion.
    – Thorsten Schöning
    Feb 10, 2021 at 14:00
  • Well, the difference is the subplans removed. Perhaps the problem is that you don't show the complete query and the complete execution plan. Perhaps that would reveal something.
    – Laurenz Albe
    Feb 10, 2021 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.