title: Postgres not using partial timestamp index on interval queries (e.g., now() - interval '7 days' )
body: <p>I have a simple table that store precipitation readings from online gauges. Here's the table definition:</p>
  
  <pre><code>    CREATE TABLE public.precip
      (
          gauge_id smallint,
          inches numeric(8, 2),
          reading_time timestamp with time zone
      )
  
      CREATE INDEX idx_precip3_id
          ON public.precip USING btree
          (gauge_id)
  
      CREATE INDEX idx_precip3_reading_time
          ON public.precip USING btree
          (reading_time)
  
  CREATE INDEX idx_precip_last_five_days
      ON public.precip USING btree
      (reading_time)
      TABLESPACE pg_default    WHERE reading_time &gt; '2017-02-26 00:00:00+00'::timestamp with time zone
  </code></pre>
  
  <p>It's grown quite large: about 38 million records that go back 18 months. Queries rarely request rows that are more than 7 days old and I created the partial index on the reading_time field so Postgres can traverse a much smaller index.  But it's not using the partial index on all queries. It <strong>does</strong> use the partial index on</p>
  
  <pre><code>explain analyze select * from precip where gauge_id = 208 and reading_time &gt; '2017-02-27' 
              Bitmap Heap Scan on precip  (cost=8371.94..12864.51 rows=1169 width=16) (actual time=82.216..162.127 rows=2046 loops=1)   
              Recheck Cond: ((gauge_id = 208) AND (reading_time &gt; '2017-02-27 00:00:00+00'::timestamp with time zone))
             -&gt;  BitmapAnd  (cost=8371.94..8371.94 rows=1169 width=0) (actual time=82.183..82.183 rows=0 loops=1)
                  -&gt;  Bitmap Index Scan on idx_precip3_id  (cost=0.00..2235.98 rows=119922 width=0) (actual time=20.754..20.754 rows=125601 loops=1)
                        Index Cond: (gauge_id = 208)
                  -&gt;  Bitmap Index Scan on idx_precip_last_five_days  (cost=0.00..6135.13 rows=331560 width=0) (actual time=60.099..60.099 rows=520867 loops=1) 
      Total runtime: 162.631 ms
  </code></pre>
  
  <p>But it does <strong>not</strong> use the partial index on the following. Instead, it's use the full index on reading_time </p>
  
  <pre><code> explain analyze select * from precip where gauge_id = 208 and reading_time &gt; now() - interval '7 days' 
  
  Bitmap Heap Scan on precip  (cost=8460.10..13007.47 rows=1182 width=16) (actual time=154.286..228.752 rows=2067 loops=1)
     Recheck Cond: ((gauge_id = 208) AND (reading_time &gt; (now() - '7 days'::interval)))
        -&gt;  BitmapAnd  (cost=8460.10..8460.10 rows=1182 width=0) (actual time=153.799..153.799 rows=0 loops=1)
                -&gt;  Bitmap Index Scan on idx_precip3_id  (cost=0.00..2235.98 rows=119922 width=0) (actual time=15.852..15.852 rows=125601 loops=1)
                     Index Cond: (gauge_id = 208)
          -&gt;  Bitmap Index Scan on idx_precip3_reading_time  (cost=0.00..6223.28 rows=335295 width=0) (actual time=136.162..136.162 rows=522993 loops=1)
                Index Cond: (reading_time &gt; (now() - '7 days'::interval))
  Total runtime: 228.647 ms
  </code></pre>
  
  <p>Note that today is 3/5/2017, so these two queries are essentially requesting the rows.  But it seems like Postgres won't use the partial index unless the timestamp in the where clause is "hard coded". Is the query planner not evaluating <code>now() - interval '7 days'</code> before deciding which index to use? I posted the query plans as suggested by one of the first people to respond.<br>
  I've written several functions (stored procedures) that summarize rain fall in the last 6 hours, 12 hours .... 72 hours.  They all use the interval approach in the query (e.g., reading_time > now() - interval '7 days'). I don't want to move this code into the application to send Postgres the hard coded timestamp.  That would create a lot of messy php code that shouldn't be necessary.</p>
  
  <p>Suggestions on how to encourage Postgres to use the partial index instead? My plan is to redefine the date range on the partial index nightly (drop index --> create index), but that seems a bit silly if Postgres isn't going to use it.</p>
  
  <p>Thanks,</p>
  
  <p>Alex</p>