title: Why select result takes long time in partitioned table in postgreSql?
body: <p>I have a daily partitioned table in postgresql. It uses cdr_date for partitioning. When I select a simple query, it takes a long time I dont know why!</p>
  
  <p>this is a simple sql</p>
  
  <pre><code>EXPLAIN (ANALYZE , BUFFERS )
  select * FROM cdr
  WHERE cdr_date &gt;= '2018-05-24 11:59:00.937000 +00:00'
    AND cdr_date &lt;= '2018-05-25 23:59:59.937000 +00:00'
  </code></pre>
  
  <p>and it result</p>
  
  <pre><code>Append  (cost=0.56..1036393.46 rows=14908437 width=295) (actual time=5019.283..335535.305 rows=15191628 loops=1)
    Buffers: shared hit=252735 read=1443977 written=125'
    -&gt;  Index Scan using ind_cdr_cdr_date on cdr  (cost=0.56..8.58 rows=1 width=286) (actual time=5019.190..5019.190 rows=0 loops=1)'
          Index Cond: ((cdr_date &gt;= ''2018-05-24 11:59:00.937+00''::timestamp with time zone) AND (cdr_date &lt;= ''2018-05-25 23:59:59.937+00''::timestamp with time zone))
          Buffers: shared hit=178464 read=708130 written=125
    -&gt;  Index Scan using ind_cdr_2018_05_24 on cdr_2018_05_24  (cost=0.43..567998.02 rows=7158579 width=295) (actual time=0.091..311773.252 rows=7846816 loops=1)
          Index Cond: ((cdr_date &gt;= ''2018-05-24 11:59:00.937+00''::timestamp with time zone) AND (cdr_date &lt;= ''2018-05-25 23:59:59.937+00''::timestamp with time zone))
          Buffers: shared hit=74264 read=383715
    -&gt;  Seq Scan on cdr_2018_05_25  (cost=0.00..468386.85 rows=7749857 width=295) (actual time=5.192..16189.737 rows=7344812 loops=1)
          Filter: ((cdr_date &gt;= ''2018-05-24 11:59:00.937+00''::timestamp with time zone) AND (cdr_date &lt;= ''2018-05-25 23:59:59.937+00''::timestamp with time zone))
          Buffers: shared hit=7 read=352132
  Planning time: 3.394 ms
  Execution time: 336984.703 ms
  </code></pre>
  
  <p>here is my root table</p>
  
  <pre><code>CREATE TABLE cdr
  (
    id                      BIGSERIAL                NOT NULL
      CONSTRAINT cdr_pkey
      PRIMARY KEY,
    username                VARCHAR(256)             NOT NULL,
    user_id                 BIGINT,
    cdr_date                TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at              TIMESTAMP WITH TIME ZONE NOT NULL,
    last_reset_time         TIMESTAMP WITH TIME ZONE,
    prev_cdr_date           TIMESTAMP WITH TIME ZONE NOT NULL
  );
  
  CREATE INDEX ind_cdr_user_id
    ON cdr (user_id);
  
  CREATE INDEX ind_cdr_cdr_date
    ON cdr (cdr_date);
  </code></pre>
  
  <p>and here is my one of the child table</p>
  
  <pre><code>-- auto-generated definition
  CREATE TABLE cdr_2018_05_25
  (
    CONSTRAINT cdr_2018_05_25_cdr_date_check
    CHECK ((cdr_date &gt;= '2018-05-25 00:00:00+00' :: TIMESTAMP WITH TIME ZONE) AND
           (cdr_date &lt;= '2018-05-26 00:23:29.064408+00' :: TIMESTAMP WITH TIME ZONE))
  )
    INHERITS (cdr);
  
  CREATE INDEX ind_cdr_2018_05_25_user_id
    ON cdr_2018_05_25 (user_id);
  
  CREATE INDEX ind_cdr_2018_05_25
    ON cdr_2018_05_25 (cdr_date);
  </code></pre>