public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alena Rybakina <[email protected]>
To: Алексей Борщёв <[email protected]>
To: [email protected]
Subject: Re: Planner makes sub-optimal execution plan
Date: Mon, 1 Sep 2025 15:41:29 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAD2_BUOH-Y4QkJiibNjD8pSin_+qCWn+vJOYfdnQdP40jzNzVw@mail.gmail.com>
References: <CAD2_BUOH-Y4QkJiibNjD8pSin_+qCWn+vJOYfdnQdP40jzNzVw@mail.gmail.com>
Hi! Thank you for sharing this interesting case!
On 01.09.2025 12:07, Алексей Борщёв wrote:
> EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SUMMARY, SETTINGS, TIMING)
> SELECT
> MIN(docum.dt) AS "dt__min",
> MAX(docum.dt_real) AS "dt_real__max"
> FROM docum
> WHERE docum.dt_real >= '2025-08-14T09:44:09.033592'::timestamp;
> -- The plan I've got is:
> Result (cost=8.38..8.39 rows=1 width=16) (actual
> time=2660.034..2660.036 rows=1 loops=1)
> Output: (InitPlan 1).col1, (InitPlan 2).col1
> Buffers: shared hit=9358751 read=30994 written=1
> InitPlan 1
> -> Limit (cost=0.43..7.91 rows=1 width=8) (actual
> time=2660.006..2660.007 rows=1 loops=1)
> Output: docum.dt
> Buffers: shared hit=9358747 read=30994 written=1
> -> Index Scan using docum_dt_7ee1d676 on public.docum
> (cost=0.43..420487.43 rows=56222 width=8) (actual
> time=2660.004..2660.005 rows=1 loops=1)
> Output: docum.dt
> Index Cond: (docum.dt IS NOT NULL)
> Filter: (docum.dt_real >= '2025-08-14
> 09:44:09.033592'::timestamp without time zone)
> Rows Removed by Filter: 11342966
> Buffers: shared hit=9358747 read=30994 written=1
> InitPlan 2
> -> Limit (cost=0.43..0.46 rows=1 width=8) (actual
> time=0.022..0.022 rows=1 loops=1)
> Output: docum_1.dt_real
> Buffers: shared hit=4
> -> Index Only Scan Backward using docum_dt_real_2b81c58c on
> public.docum docum_1 (cost=0.43..1689.22 rows=59245 width=8) (actual
> time=0.021..0.021 rows=1 loops=1)
> Output: docum_1.dt_real
> Index Cond: (docum_1.dt_real >= '2025-08-14
> 09:44:09.033592'::timestamp without time zone)
> Heap Fetches: 0
> Buffers: shared hit=4
> Settings: work_mem = '16MB', search_path = 'public, public, "$user"'
> Planning:
> Buffers: shared hit=12
> Planning Time: 0.148 ms
> Execution Time: 2660.056 ms
After disabling MIN/MAX optimization in the grouping_planner function:
/*
* Preprocess MIN/MAX aggregates, if any. Note: be careful about
* adding logic between here and the query_planner() call.
Anything
* that is needed in MIN/MAX-optimizable cases will have to be
* duplicated in planagg.c.
*/
//if (parse->hasAggs)
// preprocess_minmax_aggregates(root);
I got a better query plan, but I’m still investigating what went wrong.
Also, creating a partial index helped because of the use of indexonlyscan:
CREATE INDEX CONCURRENTLY docum_dt_recent
ON docum (dt)
WHERE dt_real >= timestamp '2025-08-01 00:00:00';
postgres=# EXPLAIN (ANALYZE, VERBOSE, BUFFERS, SUMMARY, SETTINGS, TIMING)
SELECT
MIN(docum.dt) AS "dt__min",
MAX(docum.dt_real) AS "dt_real__max"
FROM docum
WHERE docum.dt_real >= '2025-08-14T09:44:09.033592'::timestamp;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=1.11..1.12 rows=1 width=16) (actual
time=206.206..206.207 rows=1.00 loops=1)
Output: (InitPlan 1).col1, (InitPlan 2).col1
Buffers: shared hit=212507
InitPlan 1
-> Limit (cost=0.42..0.64 rows=1 width=8) (actual
time=206.154..206.155 rows=1.00 loops=1)
Output: docum.dt
Buffers: shared hit=212503
-> Index Scan using docum_dt_recent on public.docum
(cost=0.42..12391.88 rows=55710 width=8) (actual time=206.150..206.150
rows=1.00 loops=1)
Output: docum.dt
Index Cond: (docum.dt IS NOT NULL)
Filter: (docum.dt_real >= '2025-08-14
09:44:09.033592'::timestamp without time zone)
Rows Removed by Filter: 256799
Index Searches: 1
Buffers: shared hit=212503
InitPlan 2
-> Limit (cost=0.43..0.46 rows=1 width=8) (actual
time=0.042..0.042 rows=1.00 loops=1)
Output: docum_1.dt_real
Buffers: shared hit=4
-> Index Only Scan Backward using docum_dt_real on
public.docum docum_1 (cost=0.43..1671.62 rows=58696 width=8) (actual
time=0.041..0.041 rows=1.00 loops=1)
Output: docum_1.dt_real
Index Cond: (docum_1.dt_real >= '2025-08-14
09:44:09.033592'::timestamp without time zone)
Heap Fetches: 0
Index Searches: 1
Buffers: shared hit=4
Planning:
Buffers: shared hit=30 read=10
Planning Time: 2.512 ms
Execution Time: 206.290 ms
(28 rows)
--
Regards,
Alena Rybakina
Postgres Professional
view thread (3+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Planner makes sub-optimal execution plan
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox