Re: path toward faster partition pruning

On Thu, Oct 26, 2017 at 4:47 PM, Amit Langote <Langote_Amit_f8(at)lab(dot)ntt(dot)co(dot)jp
> wrote:

>
> Meanwhile, attached updated set of patches including fixes for the typos
> you reported in the other message. Updated 0005 fixes the first bug (the
> Case 1 in your email), while other patches 0002-0004 are updated mostly to
> fix the reported typos. A couple of tests are added in 0001 to test the
> default partition case a bit more.
>

Hi Amit,

while testing further this feature, I got a bug with partitions as foreign
tables. Test case given below. Take a look.

CREATE EXTENSION postgres_fdw;
CREATE SERVER fp_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname
'postgres', port '5432', use_remote_estimate 'true');
CREATE USER MAPPING FOR PUBLIC SERVER fp_server;

CREATE TABLE fplt1 (a int, b int, c text) PARTITION BY LIST(c);
CREATE TABLE fplt1_p1 (a int, b int, c text);
CREATE FOREIGN TABLE ftplt1_p1 PARTITION OF fplt1 FOR VALUES IN ('0000',
'0001', '0002', '0003') SERVER fp_server OPTIONS (TABLE_NAME 'fplt1_p1');
CREATE TABLE fplt1_p2 (a int, b int, c text);
CREATE FOREIGN TABLE ftplt1_p2 PARTITION OF fplt1 FOR VALUES IN ('0004',
'0005', '0006', '0007') SERVER fp_server OPTIONS (TABLE_NAME 'fplt1_p2');

INSERT INTO fplt1_p1 SELECT i, i, to_char(i/50, 'FM0000') FROM
generate_series(0, 198, 2) i;
INSERT INTO fplt1_p2 SELECT i, i, to_char(i/50, 'FM0000') FROM
generate_series(200, 398, 2) i;

--PG-HEAD
postgres=# EXPLAIN (COSTS OFF) SELECT t1.c FROM fplt1 t1, LATERAL (SELECT
DISTINCT t2.c FROM fplt1 t2 WHERE t2.c = t1.c ) q;
QUERY PLAN
--------------------------------------------------
Nested Loop
-> Append
-> Foreign Scan on ftplt1_p1 t1
-> Foreign Scan on ftplt1_p2 t1_1
-> Unique
-> Append
-> Foreign Scan on ftplt1_p1 t2
-> Foreign Scan on ftplt1_p2 t2_1
(8 rows)

--PG-HEAD +v5 patches
postgres=# EXPLAIN (COSTS OFF) SELECT t1.c FROM fplt1 t1, LATERAL (SELECT
DISTINCT t2.c FROM fplt1 t2 WHERE t2.c = t1.c ) q;

*ERROR: invalid expression for partition key*
Thanks & Regards,
Rajkumar Raghuwanshi
QMG, EnterpriseDB Corporation