Horton

Re: why partition pruning doesn't work?

Dmitry Dolgov <9erthalion6@gmail.com>

View thread View on postgr.es
From: Dmitry Dolgov <9erthalion6@gmail.com>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2018-06-01T13:47:31Z
Lists: pgsql-hackers

Attachments

> On 1 June 2018 at 07:19, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
> Partition pruning is working now.
>
> Is it expected? Tested on fresh master.

That's interesting. So there are two cases:

* vlozeno > (select current_date) (pruning works)

* vlozeno > current_date (pruning doesn't work)

In pull_partkey_params when we need to extract Params matching partition key in
the first case everything is fine, we've got an expr of type Param. In the
second case we've got a SQLValueFunction, which is ignored in the code - so
eventually we think that there is nothing matching a partition key and we don't
need to apply pruning.

With the attached hacky patch it would be taken into account (although I assume
in reality SQLValueFunction should be treated somehow differently) and pruning
is happening:

=# explain analyze select * from data where vlozeno > current_date;
                                                             QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..17223.38 rows=19512 width=9) (actual
time=0.456..32.952 rows=19340 loop s=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.00..14272.18 rows=8130 width=9)
(actual time=0.042..26.616 rows =6447 loops=3)
         ->  Parallel Seq Scan on data_2016  (cost=0.00..5771.19
rows=24 width=9) (never executed)
               Filter: (vlozeno > CURRENT_DATE)
         ->  Parallel Seq Scan on data_2017  (cost=0.00..5747.65
rows=23 width=9) (never executed)
               Filter: (vlozeno > CURRENT_DATE)
         ->  Parallel Seq Scan on data_other  (cost=0.00..2712.69
rows=11431 width=9) (actual time=0.032..26.031 rows=6447 loops=3)
               Filter: (vlozeno > CURRENT_DATE)
               Rows Removed by Filter: 57084
 Planning Time: 1.256 ms
 Execution Time: 35.327 ms
(13 rows)

Time: 40.291 ms

Commits

  1. Fix up run-time partition pruning's use of relcache's partition data.

  2. Fix access to just-closed relcache entry.

  3. Improve ExecFindInitialMatchingSubPlans's subplan renumbering logic.

  4. Improve commentary about run-time partition pruning data structures.

  5. Fix run-time partition pruning code to handle NULL values properly.

  6. Assorted cosmetic cleanup of run-time-partition-pruning code.

  7. Relocate partition pruning structs to a saner place.

  8. Improve run-time partition pruning to handle any stable expression.

  9. Support partition pruning at execution time