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 <andr...@a-kretschmer.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
>
>
>
