Slow plan choice with prepared query

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);