title: Postgres partition pruning
body: <p>I have a large table in Postgres. </p>
  
  <p>The table name is <code>bigtable</code> and the columns are:</p>
  
  <pre><code>integer    |timestamp   |xxx |xxx |...|xxx
  category_id|capture_time|col1|col2|...|colN
  </code></pre>
  
  <p>I have partitioned the table on modulo 10 of category_id and date part of the capture_time column. </p>
  
  <p>The partition tables look like this:</p>
  
  <pre><code>CREATE TABLE myschema.bigtable_d000h0(
      CHECK ( category_id%10=0 AND capture_time &gt;= DATE '2012-01-01' AND capture_time &lt; DATE '2012-01-02')
  ) INHERITS (myschema.bigtable);
  
  CREATE TABLE myschema.bigtable_d000h1(
      CHECK ( category_id%10=1 AND capture_time &gt;= DATE '2012-01-01' AND capture_time &lt; DATE '2012-01-02')
  ) INHERITS (myschema.bigtable);
  </code></pre>
  
  <p>When I run a query with category_id and capture_time in the where clause, the partitions are not pruned as expected.</p>
  
  <pre><code>explain select * from bigtable where capture_time &gt;= '2012-01-01' and  capture_time &lt; '2012-01-02' and category_id=100;
  
  "Result  (cost=0.00..9476.87 rows=1933 width=216)"
  "  -&gt;  Append  (cost=0.00..9476.87 rows=1933 width=216)"
  "        -&gt;  Seq Scan on bigtable  (cost=0.00..0.00 rows=1 width=210)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
  "        -&gt;  Seq Scan on bigtable_d000h0 bigtable  (cost=0.00..1921.63 rows=1923 width=216)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
  "        -&gt;  Seq Scan on bigtable_d000h1 bigtable  (cost=0.00..776.93 rows=1 width=218)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
  "        -&gt;  Seq Scan on bigtable_d000h2 bigtable  (cost=0.00..974.47 rows=1 width=216)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
  "        -&gt;  Seq Scan on bigtable_d000h3 bigtable  (cost=0.00..1351.92 rows=1 width=214)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
  "        -&gt;  Seq Scan on bigtable_d000h4 bigtable  (cost=0.00..577.04 rows=1 width=217)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
  "        -&gt;  Seq Scan on bigtable_d000h5 bigtable  (cost=0.00..360.67 rows=1 width=219)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
  "        -&gt;  Seq Scan on bigtable_d000h6 bigtable  (cost=0.00..1778.18 rows=1 width=214)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
  "        -&gt;  Seq Scan on bigtable_d000h7 bigtable  (cost=0.00..315.82 rows=1 width=216)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
  "        -&gt;  Seq Scan on bigtable_d000h8 bigtable  (cost=0.00..372.06 rows=1 width=219)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
  "        -&gt;  Seq Scan on bigtable_d000h9 bigtable  (cost=0.00..1048.16 rows=1 width=215)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100))"
  </code></pre>
  
  <p>However, if I add the exact modulo criteria (<code>category_id%10=0</code>) in the where clause, it works perfectly</p>
  
  <pre><code>explain select * from bigtable where capture_time &gt;= '2012-01-01' and  capture_time &lt; '2012-01-02' and category_id=100 and category_id%10=0;
  
  "Result  (cost=0.00..2154.09 rows=11 width=215)"
  "  -&gt;  Append  (cost=0.00..2154.09 rows=11 width=215)"
  "        -&gt;  Seq Scan on bigtable  (cost=0.00..0.00 rows=1 width=210)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100) AND ((category_id % 10) = 0))"
  "        -&gt;  Seq Scan on bigtable_d000h0 bigtable  (cost=0.00..2154.09 rows=10 width=216)"
  "              Filter: ((capture_time &gt;= '2012-01-01 00:00:00'::timestamp without time zone) AND (capture_time &lt; '2012-01-02 00:00:00'::timestamp without time zone) AND (category_id = 100) AND ((category_id % 10) = 0))"
  </code></pre>
  
  <p>Is there any way to make partition pruning work correctly without having to add the modulo condition in every query?</p>