Prepared statement query on partitioned table scans all leaf partitions even though statement has a condition to limit partitions, increasing query duration.

Products

• VMware Tanzu Greenplum

Issue/Introduction

Prepared statement query scan all partition tables although it has a condition to search the specific partitions, so query takes longer than query without the prepared statement.

For example:

Function 1

$ CREATE OR REPLACE FUNCTION public.directtest01( p_date timestamp without time zone )
RETURNS refcursor
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE ref1 refcursor = 'cur001'; 
BEGIN
OPEN ref1 FOR
SELECT count (*)
FROM	public.directtest
WHERE	startdate = p_date;
RETURN ref1;
END;
$BODY$;

Function 2

$ CREATE OR REPLACE FUNCTION public.directtest02 (
p_date timestamp without time zone )
RETURNS refcursor
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE ref1 refcursor = 'cur001'; 
BEGIN
OPEN ref1 FOR
SELECT count(*)
FROM	public.directtest
WHERE	startdate = '20010101'::timestamp
;
RETURN ref1;
END;
$BODY$;

Querying table:

Function 1:

gpadmin=# SELECT * from public.directtest01('20010101'::timestamp);
 directtest01
--------------
 cur001
(1 row)

Time: 38.096 ms
gpadmin=# fetch all from "cur001";
 count
--------
 133225
(1 row)

Time: 25522.936 ms

Function 2

gpadmin=# SELECT * from public.directtest02('20010101'::timestamp);
 directtest02
--------------
 cur001
(1 row)

Time: 40.544 ms
gpadmin=# fetch all from "cur001";
 count
--------
 133225
(1 row)

Time: 0.260 ms

This issue is present for GPDB 4.x and 5.x and with both Optimizer ON and OFF.

Environment

Product Version: 5.16

Resolution

In PostgreSQL 9.1 and below, queries in PL/pgSQL functions always used “generic” plans. A generic plan means plan that works with any parameter value, which means, no partition elimination.
 

The default behaviour now is that the query is re-planned with the parameter values the first 5 invocations. After that, a generic plan is created, and the generic plan’s cost estimate is compared with the non-generic one. If the non-generic one is cheaper, then it will keep re-planning the statement, otherwise it switches to using the generic plan for subsequent calls. In this case, the non-generic plan should be much cheaper, thanks to the partition elimination, so this case should use non-generic plans.

This should be fixed in PostgreSQL 9.2 (GPDB 6.x)