From: | Dmitry Dolgov <9erthalion6(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: why partition pruning doesn't work? |
Date: | 2018-06-01 13:47:31 |
Message-ID: | CA+q6zcV5=8JoDTBNO0EUDG7++Rj_GE89MQ1sYMHLHZs5pm3V8g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
> On 1 June 2018 at 07:19, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
Attachment | Content-Type | Size |
---|---|---|
partpruning_sql_value_function.patch | text/x-patch | 824 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Ashutosh Bapat | 2018-06-01 14:21:39 | Re: Problem while updating a foreign table pointing to a partitioned table on foreign server |
Previous Message | Amit Kapila | 2018-06-01 13:26:55 | Re: Loaded footgun open_datasync on Windows |