On Tue, Dec 16, 2014 at 1:45 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Yes, I wasn't saying that expressions should be used when *creating* the
> partitions, which strikes me as a bad idea for several reasons.
> Expressions should be usable when SELECTing data from the partitions.
> Right now, they aren't, because the planner picks parttiions well before
> the rewrite phase which would reduce "extract (month from current_date)"
> to a constant.
>
> Right now, if you partition by an integer ID even, and do:
>
> SELECT * FROM partitioned_table WHERE ID = ( 3 + 4 )
>
> ... postgres will scan all partitions because ( 3 + 4 ) is an expression
> and isn't evaluated until after CE is done.
Well, actually, that case works fine:
rhaas=# create table partitioned_table (id integer, data text);
CREATE TABLE
rhaas=# create table child1 (check (id < 1000)) inherits (partitioned_table);
CREATE TABLE
rhaas=# create table child2 (check (id >= 1000)) inherits (partitioned_table);
CREATE TABLE
rhaas=# explain select * from partitioned_table where id = (3 + 4); QUERY PLAN
------------------------------------------------------------------------Append (cost=0.00..25.38 rows=7 width=36) ->
SeqScan on partitioned_table (cost=0.00..0.00 rows=1 width=36) Filter: (id = 7) -> Seq Scan on child1
(cost=0.00..25.38rows=6 width=36) Filter: (id = 7)
(5 rows)
The reason is that 3 + 4 gets constant-folded pretty early on in the process.
But in a more complicated case where the value there isn't known until
runtime, yeah, it scans everything. I'm not sure what the best way to
fix that is. If the partition bounds were stored in a structured way,
as we've been discussing, then the Append or Merge Append node could,
when initialized, check which partition the id = X qual routes to and
ignore the rest. But that's more iffy with the current
representation, I think.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
