BUG #14715: Constraint exclusion isn't used in function using language sql

The following bug has been logged on the website:

Bug reference: 14715
Logged by: Clive Evans
Email address: cliveevans(at)ntlworld(dot)com
PostgreSQL version: 9.6.3
Operating system: CentOS Linux release 7.3.1611
Description:

The same query written using PL/PGSQL will only scan the expected partition
tables.

For example:
partition_test=# DO

$$
BEGIN
CREATE TABLE customer_reviews(
customer_id TEXT,
review_date DATE,
review_rating INTEGER,
review_votes INTEGER,
review_helpful_votes INTEGER,
product_id CHAR(10),
product_title TEXT,

product_sales_rank BIGINT,
product_group TEXT,
product_category TEXT,
product_subcategory TEXT,
similar_product_ids CHAR(10)[]
);
FOR n in 2000..2004
LOOP
EXECUTE 'CREATE TABLE customer_reviews_' || n || ' (check (review_date >=
''' || n || '-01-01'' AND review_date < ''' || n + 1 || '-01-01'')) INHERITS
(customer_reviews) ;';
END LOOP;
END
$$ ;
DO
Time: 15.647 ms
partition_test=# CREATE OR REPLACE FUNCTION title_vs_review_sql(start_date
DATE, end_date DATE)
RETURNS TABLE (
title_length_bucket INTEGER,
review_average NUMERIC,
count BIGINT
) AS
$BODY$
SELECT
width_bucket(length(product_title), 1, 50, 5) title_length_bucket,
round(avg(review_rating), 2) AS review_average,
count(*)
FROM
customer_reviews
WHERE
product_group = 'Book'
AND
review_date BETWEEN start_date AND end_date
GROUP BY
title_length_bucket
ORDER BY

title_length_bucket

$BODY$
LANGUAGE SQL;
CREATE FUNCTION
Time: 0.918 ms
partition_test=# CREATE OR REPLACE FUNCTION
title_vs_review_plpgsql(start_date DATE, end_date DATE)
RETURNS TABLE (
title_length_bucket INTEGER,
review_average NUMERIC,
count BIGINT
) AS
$BODY$
BEGIN
RETURN QUERY
SELECT
width_bucket(length(product_title), 1, 50, 5)
title_length_bucket,
round(avg(review_rating), 2) AS review_average,
count(*)
FROM
customer_reviews
WHERE
product_group = 'Book'
AND
review_date BETWEEN start_date AND end_date
GROUP BY
title_length_bucket
ORDER BY
title_length_bucket;
END;
$BODY$
LANGUAGE PLPGSQL;
CREATE FUNCTION
Time: 1.375 ms
partition_test=# set auto_explain.log_min_duration to 0;
SET
Time: 0.190 ms
partition_test=# set auto_explain.log_nested_statements to true ;
SET
Time: 0.197 ms
partition_test=# select title_vs_review_plpgsql('2001-01-01',
'2001-12-31');
title_vs_review_plpgsql
-------------------------
(0 rows)

Time: 0.899 ms
partition_test=# select title_vs_review_sql('2001-01-01', '2001-12-31');
title_vs_review_sql
---------------------
(0 rows)

Time: 0.949 ms

When we check the logs for the query plans, I expect them both to only scan
the one child table, however:
< 2017-06-21 13:40:21.086 UTC > LOG: duration: 0.013 ms plan:
Query Text: SELECT
width_bucket(length(product_title), 1, 50, 5) title_length_bucket,
round(avg(review_rating), 2) AS review_average,
count(*)
FROM
customer_reviews
WHERE
product_group = 'Book'
AND
review_date BETWEEN start_date AND end_date
GROUP BY
title_length_bucket
ORDER BY
title_length_bucket
GroupAggregate (cost=14.95..15.01 rows=2 width=44) (actual
time=0.011..0.011 rows=0 loops=1)
Group Key: (width_bucket((length(customer_reviews.product_title))::double
precision, '1'::double precision, '50'::double precision, 5))
-> Sort (cost=14.95..14.95 rows=2 width=8) (actual time=0.010..0.010
rows=0 loops=1)
Sort Key:
(width_bucket((length(customer_reviews.product_title))::double precision,
'1'::double precision, '50'::double precision, 5))
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..14.94 rows=2 width=8) (actual
time=0.005..0.005 rows=0 loops=1)
-> Append (cost=0.00..14.90 rows=2 width=36) (actual
time=0.004..0.004 rows=0 loops=1)
-> Seq Scan on customer_reviews (cost=0.00..0.00
rows=1 width=36) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((review_date >= '2001-01-01'::date) AND
(review_date <= '2001-12-31'::date) AND (product_group = 'Book'::text))
-> Seq Scan on customer_reviews_2001
(cost=0.00..14.90 rows=1 width=36) (actual time=0.000..0.000 rows=0
loops=1)
Filter: ((review_date >= '2001-01-01'::date) AND
(review_date <= '2001-12-31'::date) AND (product_group = 'Book'::text))
< 2017-06-21 13:40:21.086 UTC > CONTEXT: PL/pgSQL function
title_vs_review_plpgsql(date,date) line 3 at RETURN QUERY
< 2017-06-21 13:40:21.086 UTC > LOG: duration: 0.640 ms plan:
Query Text: select title_vs_review_plpgsql('2001-01-01', '2001-12-31');
Result (cost=0.00..5.25 rows=1000 width=32) (actual time=0.636..0.636
rows=0 loops=1)
< 2017-06-21 13:40:26.869 UTC > LOG: duration: 0.030 ms plan:
Query Text:
SELECT
width_bucket(length(product_title), 1, 50, 5) title_length_bucket,
round(avg(review_rating), 2) AS review_average,
count(*)
FROM
customer_reviews
WHERE
product_group = 'Book'
AND
review_date BETWEEN start_date AND end_date
GROUP BY
title_length_bucket
ORDER BY
title_length_bucket


GroupAggregate (cost=74.68..74.88 rows=6 width=44) (actual
time=0.028..0.028 rows=0 loops=1)
Group Key: (width_bucket((length(customer_reviews.product_title))::double
precision, '1'::double precision, '50'::double precision, 5))
-> Sort (cost=74.68..74.70 rows=6 width=8) (actual time=0.026..0.026
rows=0 loops=1)
Sort Key:
(width_bucket((length(customer_reviews.product_title))::double precision,
'1'::double precision, '50'::double precision, 5))
Sort Method: quicksort Memory: 25kB
-> Result (cost=0.00..74.61 rows=6 width=8) (actual
time=0.007..0.007 rows=0 loops=1)
-> Append (cost=0.00..74.50 rows=6 width=36) (actual
time=0.007..0.007 rows=0 loops=1)
-> Seq Scan on customer_reviews (cost=0.00..0.00
rows=1 width=36) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((review_date >= $1) AND (review_date <=
$2) AND (product_group = 'Book'::text))
-> Seq Scan on customer_reviews_2000
(cost=0.00..14.90 rows=1 width=36) (actual time=0.001..0.001 rows=0
loops=1)
Filter: ((review_date >= $1) AND (review_date <=
$2) AND (product_group = 'Book'::text))
-> Seq Scan on customer_reviews_2001
(cost=0.00..14.90 rows=1 width=36) (actual time=0.001..0.001 rows=0
loops=1)
Filter: ((review_date >= $1) AND (review_date <=
$2) AND (product_group = 'Book'::text))
-> Seq Scan on customer_reviews_2002
(cost=0.00..14.90 rows=1 width=36) (actual time=0.000..0.000 rows=0
loops=1)
Filter: ((review_date >= $1) AND (review_date <=
$2) AND (product_group = 'Book'::text))
-> Seq Scan on customer_reviews_2003
(cost=0.00..14.90 rows=1 width=36) (actual time=0.001..0.001 rows=0
loops=1)
Filter: ((review_date >= $1) AND (review_date <=
$2) AND (product_group = 'Book'::text))
-> Seq Scan on customer_reviews_2004
(cost=0.00..14.90 rows=1 width=36) (actual time=0.001..0.001 rows=0
loops=1)
Filter: ((review_date >= $1) AND (review_date <=
$2) AND (product_group = 'Book'::text))
< 2017-06-21 13:40:26.869 UTC > CONTEXT: SQL function "title_vs_review_sql"
statement 1
< 2017-06-21 13:40:26.869 UTC > LOG: duration: 0.684 ms plan:
Query Text: select title_vs_review_sql('2001-01-01', '2001-12-31');
Result (cost=0.00..5.25 rows=1000 width=32) (actual time=0.680..0.680
rows=0 loops=1)