title: Partition by date range PostgreSQL scans all partitions
body: <p>I have a table partitioned per month (timestamp column). </p>
  
  <p>when querying the data, explain shows that all partitions are being queried when I'm constructing a date with date functions, whereas when I use hard coded dates only the targeted partitions are being scanned.</p>
  
  <p>So when querying like this:</p>
  
  <pre><code>SELECT * FROM vw_comments 
        WHERE created &gt;= '2019-4-1'
          AND created &lt;= '2019-4-30'
  limit 100;
  </code></pre>
  
  <p>it only scans 1 partition (1 month, good!)
  but then to make it more dynamic I'm passing in something like this (simplified)</p>
  
  <pre><code>SELECT * FROM vw_comments 
        WHERE created &gt;= (date_trunc('month', now()))::timestamp
          AND created &lt;= (date_trunc('month', now() + interval '1 month') - interval '1 day') ::timestamp
  limit 100;
  </code></pre>
  
  <p>the exact same dates come out of the above date methods as the first query, but EXPLAIN shows all partitions get scanned. </p>
  
  <p>How to make it work?</p>
  
  <p><strong>edit: add table definition and explain</strong></p>
  
  <p>upon request from @a_horse_with_no_name, I added the actual table and explain. Upon doing that I figured out something more: dynamic dates don't work when doing a join. So leaving out the 'users' table in the below query makes dynamic dates work.</p>
  
  <pre><code>CREATE TABLE public.comments
  (
      comment_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
      comment_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY,
      from_user_id integer NOT NULL,
      fk_topic_id integer NOT NULL,
      comment_text text COLLATE pg_catalog."default",
      parent_comment_id integer,
      created timestamp without time zone NOT NULL,
      comment_type integer NOT NULL DEFAULT 0,
      CONSTRAINT comments_pkey PRIMARY KEY (comment_id, created)
  ) PARTITION BY RANGE (created) 
  WITH (
      OIDS = FALSE
  )
  TABLESPACE pg_default;
  
  ALTER TABLE public.comments
      OWNER to soichat;
  
  CREATE INDEX ix_comments_comment_id
      ON public.comments USING btree
      (comment_id DESC)
      TABLESPACE pg_default;
  
  CREATE INDEX ix_comments_created
      ON public.comments USING btree
      (created DESC)
      TABLESPACE pg_default;
  
  CREATE INDEX ix_comments_fk_topic_id
      ON public.comments USING btree
      (fk_topic_id)
      TABLESPACE pg_default;
  
  CREATE INDEX ix_comments_from_user_id
      ON public.comments USING btree
      (from_user_id)
      TABLESPACE pg_default;
  
  CREATE INDEX ix_comments_parent_comment_id
      ON public.comments USING btree
      (parent_comment_id)
      TABLESPACE pg_default;
  
  -- Partitions SQL
  
  CREATE TABLE public.comments_2019_2 PARTITION OF public.ix_comments_parent_comment_id
      FOR VALUES FROM ('2019-02-01 00:00:00') TO ('2019-03-01 00:00:00');
  
  CREATE TABLE public.comments_2019_3 PARTITION OF public.ix_comments_parent_comment_id
      FOR VALUES FROM ('2019-03-01 00:00:00') TO ('2019-04-01 00:00:00');
  
  CREATE TABLE public.comments_2019_4 PARTITION OF public.ix_comments_parent_comment_id
      FOR VALUES FROM ('2019-04-01 00:00:00') TO ('2019-05-01 00:00:00');
  
  CREATE TABLE public.comments_2019_5 PARTITION OF public.ix_comments_parent_comment_id
      FOR VALUES FROM ('2019-05-01 00:00:00') TO ('2019-06-01 00:00:00');
  </code></pre>
  
  <p>the query:</p>
  
  <pre><code>explain (analyse, buffers)
   SELECT comments.comment_id,
      comments.from_user_id,
      comments.fk_topic_id,
      comments.comment_text,
      comments.parent_comment_id,
      comments.created,
      users.user_name,
      users.picture_path
     FROM comments
       LEFT JOIN users ON comments.from_user_id = users.user_id
      WHERE comments.created &gt;= (date_trunc('month', now()))::timestamp
          AND comments.created &lt;= (date_trunc('month', now() + interval '1 month') - interval '1 day') ::timestamp
  limit 100;
  </code></pre>
  
  <p><a href="https://explain.depesz.com/s/K6wh" rel="noreferrer">explain (analyze, buffers)</a> </p>
  
  <pre><code>Limit  (cost=1.20..11.93 rows=100 width=126) (actual time=1.441..1.865 rows=100 loops=1)
    Buffers: shared hit=499
    -&gt;  Merge Left Join  (cost=1.20..753901.07 rows=7028011 width=126) (actual time=1.440..1.778 rows=100 loops=1)
          Merge Cond: (comments_2019_2.from_user_id = users.user_id)
          Buffers: shared hit=499
          -&gt;  Merge Append  (cost=0.92..665812.08 rows=7028011 width=51) (actual time=0.017..0.259 rows=100 loops=1)
                Sort Key: comments_2019_2.from_user_id
                Buffers: shared hit=15
                -&gt;  Index Scan using comments_2019_2_from_user_id_idx on comments_2019_2  (cost=0.15..58.95 rows=5 width=56) (actual time=0.002..0.003 rows=0 loops=1)
                      Filter: ((created &gt;= (date_trunc('month'::text, now()))::timestamp without time zone) AND (created &lt;= ((date_trunc('month'::text, (now() + '1 mon'::interval)) - '1 day'::interval))::timestamp without time zone))
                      Buffers: shared hit=1
                -&gt;  Index Scan using comments_2019_3_from_user_id_idx on comments_2019_3  (cost=0.15..9790.24 rows=1 width=51) (actual time=0.002..0.003 rows=0 loops=1)
                      Filter: ((created &gt;= (date_trunc('month'::text, now()))::timestamp without time zone) AND (created &lt;= ((date_trunc('month'::text, (now() + '1 mon'::interval)) - '1 day'::interval))::timestamp without time zone))
                      Buffers: shared hit=1
                -&gt;  Index Scan using comments_2019_4_from_user_id_idx on comments_2019_4  (cost=0.43..550483.74 rows=7028000 width=51) (actual time=0.010..0.162 rows=100 loops=1)
                      Filter: ((created &gt;= (date_trunc('month'::text, now()))::timestamp without time zone) AND (created &lt;= ((date_trunc('month'::text, (now() + '1 mon'::interval)) - '1 day'::interval))::timestamp without time zone))
                      Buffers: shared hit=12
                -&gt;  Index Scan using comments_2019_5_from_user_id_idx on comments_2019_5  (cost=0.15..58.95 rows=5 width=56) (actual time=0.001..0.002 rows=0 loops=1)
                      Filter: ((created &gt;= (date_trunc('month'::text, now()))::timestamp without time zone) AND (created &lt;= ((date_trunc('month'::text, (now() + '1 mon'::interval)) - '1 day'::interval))::timestamp without time zone))
                      Buffers: shared hit=1
          -&gt;  Index Scan using pk_users on users  (cost=0.28..234.83 rows=1606 width=79) (actual time=0.005..0.870 rows=1395 loops=1)
                Buffers: shared hit=484
  Planning Time: 0.360 ms
  Execution Time: 1.942 ms
  </code></pre>