Horton

Re: path toward faster partition pruning

Amit Langote <langote_amit_f8@lab.ntt.co.jp>

View thread View on postgr.es
From: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>
To: Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>
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-27T09:11:25Z
Lists: pgsql-hackers

Attachments

Thanks Rajkumar for the test case.

On 2017/10/27 17:05, Rajkumar Raghuwanshi wrote:
> while testing further this feature, I got a bug with partitions as foreign
> tables. Test case given below. Take a look.

[ ... ]

> 
> --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*

I looked at this and it seems the error occurs not because partitions
being foreign tables, but because the new code is wrong to assume that
Param nodes can never appear in the clauses coming from baserestrictinfo.
When trying to do the plan-time pruning for the partitioned table
appearing inside the lateral subquery, there are Params in the clauses in
baserestrictinfo that the new pruning code was unprepared to handle.
Fixed the code to instead give up on plan-time pruning in such a case.

Attached updated set of patches.  In addition to fixing the above bug, it
also fixes one of the cases reported by Beena regarding default partition
pruning that I yesterday had given up on as being too difficult to
implement [1], but today found out is not that difficult to do [2].
Change summary:

0001: added some new tests
0002: no change
0003: fixed issue that Rajkumar reported (cope with Params properly)
0004: no change
0005: fix the case to prune the default partition when warranted (the
      issue reported by Beena)

Thanks,
Amit

[1]
https://www.postgresql.org/message-id/0d6096e8-7c7b-afed-71d3-dca151306626%40lab.ntt.co.jp

[2]
https://www.postgresql.org/message-id/8499324c-8a33-4be7-9d23-7e6a95e60ddf%40lab.ntt.co.jp

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.