Re: postgresql 10.1 wrong plan in when using partitions bug

Am 04.02.2018 um 13:19 schrieb Mariel Cherkassky:
> 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.


Regards, Andreas

-- 
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com