Re: postgresql 10.1 wrong plan in when using partitions bug

Great, it solved the issue. Seems problematic that the planner do full
scans on all partitions in the first case isnt it ? Seems like a bug ?

2018-02-04 16:54 GMT+02:00 Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>:

>
>
> 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
>
>
>