title: PostgreSQL 13: Efficient use of partitions and tsrange
body: <p>I am trying to figure out how to make the most efficient usage of table partitions and the tsrange function. I am using PostgreSQL 13.</p>
  <p>I will post here an example knowing that it is not efficient and understanding why but I am looking for advice on how to make this efficient and tuned.
  In the example below all partitions are read every time I run the select query.
  I would like to find if a boat trip overlaps with another one in a booking calendar.</p>
  <pre><code>CREATE TABLE boat_trips (
     id INTEGER NOT NULL
     , boat_name VARCHAR(32)
     , departure_time TIMESTAMP WITHOUT TIME ZONE  NOT NULL
     , destination_time TIMESTAMP WITHOUT TIME ZONE NOT NULL
  ) 
  PARTITION BY RANGE (departure_time);
                            
  CREATE TABLE IF NOT EXISTS boat_trips_20210206 PARTITION OF boat_trips FOR VALUES FROM ('2021-02-06 00:00:00') TO ('2021-02-06 23:59:59');
  CREATE TABLE IF NOT EXISTS boat_trips_20210207 PARTITION OF boat_trips FOR VALUES FROM ('2021-02-07 00:00:00') TO ('2021-02-07 23:59:59');
  CREATE TABLE IF NOT EXISTS boat_trips_20210208 PARTITION OF boat_trips FOR VALUES FROM ('2021-02-08 00:00:00') TO ('2021-02-08 23:59:59');
                             
  INSERT INTO boat_trips VALUES (1, 'The Beautiful', '2021-02-06 11:15:00'::TIMESTAMP, '2021-02-06 12:15:00'::TIMESTAMP);
  INSERT INTO boat_trips VALUES (2, 'The Incredible', '2021-02-06 13:15:00'::TIMESTAMP, '2021-02-06 14:15:00'::TIMESTAMP);
  INSERT INTO boat_trips VALUES (3, 'The Beautiful', '2021-02-06 12:30:00'::TIMESTAMP, '2021-02-06 13:15:00'::TIMESTAMP);
  INSERT INTO boat_trips VALUES (4, 'The Beautiful', '2021-02-07 11:15:00'::TIMESTAMP, '2021-02-07 12:15:00'::TIMESTAMP);
  INSERT INTO boat_trips VALUES (5, 'The Incredible', '2021-02-07 13:15:00'::TIMESTAMP, '2021-02-07 14:15:00'::TIMESTAMP);
  INSERT INTO boat_trips VALUES (6, 'The Beautiful', '2021-02-07 12:30:00'::TIMESTAMP, '2021-02-07 13:15:00'::TIMESTAMP);
  INSERT INTO boat_trips VALUES (7, 'The Beautiful', '2021-02-08 11:15:00'::TIMESTAMP, '2021-02-08 12:15:00'::TIMESTAMP);
  INSERT INTO boat_trips VALUES (8, 'The Incredible', '2021-02-08 13:15:00'::TIMESTAMP, '2021-02-08 14:15:00'::TIMESTAMP);
  INSERT INTO boat_trips VALUES (9, 'The Beautiful', '2021-02-08 12:30:00'::TIMESTAMP, '2021-02-08 13:15:00'::TIMESTAMP);
  </code></pre>
  <p>Then I run the select query as follows:</p>
  <pre><code>SELECT DISTINCT bt.id, bt.boat_name, bt.departure_time, bt.destination_time
  FROM  boat_trips bt
  WHERE  tsrange(departure_time, destination_time) &amp;&amp;
         tsrange '[2021-02-07 00:00:00,2021-02-08 00:00:00)';
  </code></pre>
  <p>If I get the explain plan, I find that all partitions are parsed and it is normal because the partition key id departure_time.</p>
  <p>How could I take advantage of the partitioning and the <code>tsrange</code> function in my select query?</p>
  <p>I tried to partion on <code>tsrange(departure_time, destination_time)</code> which works but then I can't find the syntax to create the partitions.</p>
  <p>There is an example here =&gt; <a href="https://www.db-fiddle.com/f/hMh4F53937ZyjZJqa5BjZE/5" rel="nofollow noreferrer">db fiddle</a></p>