Horton

Re: path toward faster partition pruning

Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>

View thread View on postgr.es
From: Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>
To: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
Cc: Beena Emerson <memissemerson@gmail.com>, David Rowley <david.rowley@2ndquadrant.com>, Dilip Kumar <dilipbalaut@gmail.com>, Pg Hackers <pgsql-hackers@postgresql.org>
Date: 2017-10-27T08:05:39Z
Lists: pgsql-hackers 
On Thu, Oct 26, 2017 at 4:47 PM, Amit Langote <Langote_Amit_f8@lab.ntt.co.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

Commits

  1. Fix assorted partition pruning bugs

  2. Make gen_partprune_steps static

  3. Remove useless 'default' clause

  4. Reorganize partitioning code

  5. Use custom hash opclass for hash partition pruning

  6. Blindly attempt to fix sepgsql tests broken due to 9fdb675fc5.

  7. Attempt to fix endianess issues in new hash partition test.

  8. Faster partition pruning

  9. For partitionwise join, match on partcollation, not parttypcoll.

  10. Revise API for partition bound search functions.

  11. Revise API for partition_rbound_cmp/partition_rbound_datum_cmp.

  12. Fix possible crash in partition-wise join.

  13. Refactor code for partition bound searching

  14. New C function: bms_add_range

  15. Add extensive tests for partition pruning.

  16. Add null test to partition constraint for default range partitions.

  17. Remove BufFile's isTemp flag.

  18. Make OWNER TO subcommand mention consistent

  19. Fix index matching for operators with mixed collatable/noncollatable inputs.