Re: [GENERAL] 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