0

Im running postgres 16.1 with a single user and no other activity on the server. I run this Test for many times and the results are very consistent.

The Problem

The query below will run with very consistent timings and the same execution plan (independt if server was started 3 seconds ago or run for weeks). But the total execution time is dependend if the query will be run from a SQL-client or from psql on the server.

The timing from any SQL-Client is about 3 times bigger in comparison to psql. But if i run the same query in a SQL-client but with

explain analyse verbose timings wal buffers

the time is very comparable with psql execution.

The observation I made is, the execution over psql uses 5 cores corresponding to the setting ax_parallel_workers_per_gather=5. On the other hand, the execution from SQL-Client only uses 1 core and the execution time is about 90 seconds istead of 30.

Can you help me to elaborate on this?

2024-02-01 09:56:25.828 CET [650041] postgres@E_LOG:  duration: 29491.907 ms  plan:
    Query Text: WITH
    "x0" AS
        (
        SELECT
            "x1"."cal_dateid" AS "cal_dateid",
            "x1"."cal_dateint" AS "cal_dateint",
            "x1"."cal_date" AS "cal_date",
            "x1"."cal_year" AS "cal_year",
            "x1"."cal_month" AS "cal_month",
            "x1"."cal_day" AS "cal_day",
            "x1"."cal_yearmonth" AS "cal_yearmonth",
            "x1"."cal_datetext" AS "cal_datetext",
            "x1"."cal_yearmonthtext" AS "cal_yearmonthtext",
            "x1"."cal_monthtext" AS "cal_monthtext",
            extract(week from "x1"."cal_date") AS "c_date",
            "x1"."QWE" AS "QWE"
        FROM
            "public"."ABC" "x1"
        ),
    "DEF" AS
        (
        SELECT
            "x0"."cal_date" AS "cal_date",
            "x0"."cal_year" AS "cal_year"
        FROM
            "x0"
        )
    SELECT
        "DEF"."cal_year" AS "intr_year",
        SUM("ABC_ALL"."ID") AS "ID",
        COUNT(DISTINCT "ABC_ALL"."id") AS "id_sing",
        SUM("CDC"."SUM") AS "SUM_1"
    FROM
        "public"."ABC_2" "ABC_ALL"
            LEFT OUTER JOIN "DEF"
            ON "DEF"."cal_date" = "ABC_ALL"."date_occ"
                LEFT OUTER JOIN "public"."case" "CDC"
                ON "CDC"."id_fk" = "ABC_ALL"."occ_id"
    GROUP BY
        "DEF"."cal_year"
    ORDER BY
        "intr_year" DESC NULLS last;
    GroupAggregate  (cost=1753092.95..5239798.05 rows=151 width=28)
      Group Key: x1.cal_year
      ->  Gather Merge  (cost=1753092.95..4973483.11 rows=26631343 width=26)
            Workers Planned: 5
            ->  Sort  (cost=1752092.87..1765408.54 rows=5326269 width=26)
                  Sort Key: x1.cal_year DESC NULLS LAST, ABC_ALL.id
                  ->  Hash Left Join  (cost=164169.92..1084201.62 rows=5326269 width=26)
                        Hash Cond: (ABC_ALL.date_occ = x1.cal_date)
                        ->  Parallel Hash Right Join  (cost=162083.00..1068132.16 rows=5326269 width=26)
                              Hash Cond: (CDC.id_fk = ABC_ALL.occ_id)
                              ->  Parallel Seq Scan on case CDC  (cost=0.00..892067.69 rows=5326269 width=12)
                              ->  Parallel Hash  (cost=155333.00..155333.00 rows=540000 width=22)
                                    ->  Parallel Seq Scan on ABC_2 ABC_ALL  (cost=0.00..155333.00 rows=540000 width=22)
                        ->  Hash  (cost=1397.52..1397.52 rows=55152 width=8)
                              ->  Seq Scan on ABC x1  (cost=0.00..1397.52 rows=55152 width=8)
    JIT:
      Functions: 22
      Options: Inlining true, Optimization true, Expressions true, Deforming true

1 Answer 1

2

You don't tell us many relevant details, but I guess that the “SQL client” is DBeaver or something else that uses the JDBC driver and limits the number of result rows with java.sql.Statement.setMaxRows(). Doing that disables the use of parallel query in PostgreSQL, which may well explain your performance problem.

See my article for details.

1
  • Thank you! This was exactly the issue, albeit the problem occured not in DBeaver.
    – 0xbadc0de
    Feb 2 at 15:56

Your Answer

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

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