I checked the plan of the next query : explain select count(*) from log_full where end_date between to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/03','YY/MM/DD');
can you rewrite the query to
... where end_date between '2017/12/03' and '2017/12/03'
simple test-case:
test=*# \d+ t Table "public.t" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+------+-----------+----------+---------+---------+--------------+------------- d | date | | | | plain | | Partition key: RANGE (d) Partitions: t_01 FOR VALUES FROM ('2018-02-04') TO ('2018-02-05'), t_02 FOR VALUES FROM ('2018-02-05') TO ('2018-02-06')
test=*# explain analyse select * from t where d between to_date('2018/02/04','YY/MM/DD') and to_date('2018/02/04','YY/MM/DD'); QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- Append (cost=0.00..122.00 rows=26 width=4) (actual time=0.006..0.006 rows=0 loops=1) -> Seq Scan on t_01 (cost=0.00..61.00 rows=13 width=4) (actual time=0.004..0.004 rows=0 loops=1) Filter: ((d >= to_date('2018/02/04'::text, 'YY/MM/DD'::text)) AND (d <= to_date('2018/02/04'::text, 'YY/MM/DD'::text))) -> Seq Scan on t_02 (cost=0.00..61.00 rows=13 width=4) (actual time=0.001..0.001 rows=0 loops=1) Filter: ((d >= to_date('2018/02/04'::text, 'YY/MM/DD'::text)) AND (d <= to_date('2018/02/04'::text, 'YY/MM/DD'::text))) Planning time: 0.241 ms Execution time: 0.042 ms (7 rows)
test=*# explain analyse select * from t where d between '2018/02/04' and '2018/02/04'; QUERY PLAN ------------------------------------------------------------------------------------------------------ Append (cost=0.00..48.25 rows=13 width=4) (actual time=0.005..0.005 rows=0 loops=1) -> Seq Scan on t_01 (cost=0.00..48.25 rows=13 width=4) (actual time=0.004..0.004 rows=0 loops=1) Filter: ((d >= '2018-02-04'::date) AND (d <= '2018-02-04'::date)) Planning time: 0.203 ms Execution time: 0.030 ms (5 rows)
test=*#
maybe the planner should be smart enough to do that for you, but obvously he can't. So it's a workaround, but it seems to solve the problem.
