Message-ID: <6a1865a0-0379-d575-a7d7-87b01852d997@manse.cloud>
Date: Thu, 24 Feb 2022 14:37:59 +1100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:98.0)
 Gecko/20100101 Thunderbird/98.0
Content-Language: en-US
To: pgsql-performance@lists.postgresql.org
From: Mark Saward <mark@manse.cloud>
Subject: Slow plan choice with prepared query
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/6a1865a0-0379-d575-a7d7-87b01852d997%40manse.cloud>
Precedence: bulk

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