From: Mark Saward <mark@manse.cloud>
To: pgsql-performance@lists.postgresql.org
Subject: Slow plan choice with prepared query
Date: Thu, 24 Feb 2022 14:37:59 +1100
Message-ID: <6a1865a0-0379-d575-a7d7-87b01852d997@manse.cloud> (raw)
Hi,
I've experienced a situation where the planner seems to make a very poor
choice with a prepared query after the first five executions. Looking
at the documentation, I think this happens because it switches from a
custom plan to a generic one, and doesn't make a good choice for the
generic one.
Postgres version: running in docker, reports to be 'Debian 14.1-1.pgdg110+1'
If I force it to use a custom plan via 'set local plan_cache_mode =
force_custom_plan', then I don't notice any slowdown. Without it, the
6th and onwards calls can take 1 second to 15 seconds each, as opposed
to about 10ms.
Since I have a workaround, I don't necessarily need assistance, but
posting this here in case it's of value as a test case. Here's a test
case that reliably duplicates this issue for me:
----
create table test (
test_id serial primary key,
data text
);
insert into test (data) (select data from (select
generate_series(1,10000) AS id, md5(random()::text) AS data) x);
prepare foo_test(text, text, int, text, bool) as SELECT * FROM (SELECT
*,
count(*) OVER () > $3 AS has_more,
row_number() OVER ()
FROM (
WITH counted AS (
SELECT count(*) AS total
FROM (select test_id::text, data
from test
where
(cast($1 as text) is null or lower(data) like '%' || lower($1) || '%')
and
(cast($2 as text) is null or lower(test_id::text) like '%' ||
lower($2) || '%')) base
), cursor_row AS (
SELECT base.test_id
FROM (select test_id::text, data
from test
where
(cast($1 as text) is null or lower(data) like '%' || lower($1) || '%')
and
(cast($2 as text) is null or lower(test_id::text) like '%' ||
lower($2) || '%')) base
WHERE base.test_id = $4
)
SELECT counted.*, base.*
FROM (select test_id::text, data
from test
where
(cast($1 as text) is null or lower(data) like '%' || lower($1) || '%')
and
(cast($2 as text) is null or lower(test_id::text) like '%' ||
lower($2) || '%')) base
LEFT JOIN cursor_row ON true
LEFT JOIN counted ON true
WHERE ((
$4 IS NULL OR cast($5 as bool) IS NULL
) OR (
(base.test_id)
> (cursor_row.test_id)
))
ORDER BY base.test_id ASC
LIMIT $3 + 1
) xy LIMIT $3 ) z ORDER BY row_number ASC;
\timing
execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);
execute foo_test(null, null, 5, 500, true);
-- This one should be slower:
execute foo_test(null, null, 5, 500, true);
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: pgsql-performance@postgresql.org
Cc: mark@manse.cloud, pgsql-performance@lists.postgresql.org
Subject: Re: Slow plan choice with prepared query
In-Reply-To: <6a1865a0-0379-d575-a7d7-87b01852d997@manse.cloud>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox