Re: Partitioning (constraint exclusion involving joins)

On Tue, Jul 25, 2017 at 06:21:43PM +0530, Krithika Venkatesh wrote:
> I have a table that is partitioned on a numeric column (ID).
>
> Partitioning works when I query the table with no joins.
>
> SELECT * FROM TABLE A a WHERE ID IN (SELECT ID FROM TABLE B b WHERE
> CREATED_TS = CURRENT_TIMESTAMP)
>
> Partitioning doesn't work when I do join.
>
> SELECT A.* FROM TABLE A a INNER JOIN TABLE B b ON a.ID = b.ID.

I think you mean "constraint exclusion doesn't work when yo do a join",

which is because it only works on simple values compiled before the planner
gets to see them:

main=# explain SELECT COUNT(1) FROM eric_enodeb_metrics WHERE start_time>now(); -- -'999 minutes'::interval;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=62.44..62.45 rows=1 width=8)
-> Append (cost=0.00..62.40 rows=14 width=0)
-> Seq Scan on eric_enodeb_metrics (cost=0.00..0.00 rows=1 width=0)
Filter: (start_time > now())
-> Index Only Scan using eric_enodeb_201607_idx on eric_enodeb_201607 (cost=0.42..4.44 rows=1 width=0)
Index Cond: (start_time > now())
-> Index Only Scan using eric_enodeb_201608_idx on eric_enodeb_201608 (cost=0.42..4.44 rows=1 width=0)
Index Cond: (start_time > now())
-> Index Only Scan using eric_enodeb_201609_idx on eric_enodeb_201609 (cost=0.42..4.44 rows=1 width=0)
Index Cond: (start_time > now())

https://www.postgresql.org/docs/current/static/ddl-partitioning.html
|The following caveats apply to constraint exclusion:
| Constraint exclusion only works when the query's WHERE clause contains
|constants (or externally supplied parameters). For example, a comparison
|against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized,
|since the planner cannot know which partition the function value might fall
|into at run time.
[..]

.. and see an early mail on its implementation, here:
https://www.postgresql.org/message-id/1121251997.3970.237.camel@localhost.localdomain

Justin