From: | MichaelDBA <MichaelDBA(at)sqlexec(dot)com> |
---|---|
To: | Mark Saward <mark(at)manse(dot)cloud> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Slow plan choice with prepared query |
Date: | 2022-02-24 18:45:57 |
Message-ID: | 22689907-c5f1-eb8a-f567-6d55abfaf163@sqlexec.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Dag, if you ain't right! I can duplicate this on the ones I tested
with: PG v11 and v14. Gonna start diving into this myself...
Regards,
Michael Vitale
Mark Saward wrote on 2/23/2022 10:37 PM:
> 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);
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | MichaelDBA | 2022-02-24 19:55:34 | Re: Slow plan choice with prepared query |
Previous Message | Tom Lane | 2022-02-24 16:10:51 | Re: Advice needed: query performance deteriorates by 2000% within 1 minute |