Re: postgresql 10.1 wrong plan in when using partitions bug

Output of explain analyze :

explain analyze select count(*) from log_full where end_date between
to_date('2017/12/03','YY/MM/DD') and to_date('2017/12/04','YY/MM/DD');

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------
Finalize Aggregate (cost=38058211.38..38058211.39 rows=1 width=8) (actual
time=3502304.726..3502304.726 rows=1 loops=1)
-> Gather (cost=38058211.16..38058211.37 rows=2 width=8) (actual
time=3502179.810..3502251.520 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=38057211.16..38057211.17 rows=1
width=8) (actual time=3500338.084..3500338.084 rows
=1 loops=3)
-> Append (cost=0.00..38040836.26 rows=6549963 width=0)
(actual time=1513398.593..3499538.302 rows=52402
29 loops=3)
-> Parallel Seq Scan on
log_full_1_11_2017_action_status (cost=0.00..39863.21 rows=1 width=
0) (actual time=4047.915..4047.915 rows=0 loops=3)
Filter: ((end_date >=
to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
'2017/12/04'::text, 'YY/MM/DD'::text)))
Rows Removed by Filter: 286924
-> Parallel Seq Scan on
log_full_1_11_2017_alive_status (cost=0.00..702893.03 rows=1 width=
0) (actual time=63648.476..63648.476 rows=0 loops=3)
Filter: ((end_date >=
to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
'2017/12/04'::text, 'YY/MM/DD'::text)))
Rows Removed by Filter: 4955092
-> Parallel Seq Scan on
log_full_1_11_2017_modem_status (cost=0.00..10.59 rows=1 width=0) (
actual time=0.001..0.001 rows=0 loops=3)
Filter: ((end_date >=
to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
'2017/12/04'::text, 'YY/MM/DD'::text)))

....................

and so on full on on partitions..

2018-02-04 15:43 GMT+02:00 Mariel Cherkassky <mariel(dot)cherkassky(at)gmail(dot)com>:

> explain analyze takes too much time.. hours ...
> I run it now but it will take some time.
> The output of the explain :
>
> Finalize Aggregate (cost=38058211.38..38058211.39 rows=1 width=8)
> -> Gather (cost=38058211.16..38058211.37 rows=2 width=8)
> Workers Planned: 2
> -> Partial Aggregate (cost=38057211.16..38057211.17 rows=1
> width=8)
> -> Append (cost=0.00..38040836.26 rows=6549963 width=0)
> -> Parallel Seq Scan on log_full_1_11_2017_action_status
> (cost=0.00..39863.21 rows=1 width=
> 0)
> Filter: ((end_date >=
> to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
> '2017/12/04'::text, 'YY/MM/DD'::text)))
> -> Parallel Seq Scan on log_full_1_11_2017_alive_status
> (cost=0.00..702893.03 rows=1 width=
> 0)
> Filter: ((end_date >=
> to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
> '2017/12/04'::text, 'YY/MM/DD'::text)))
> -> Parallel Seq Scan on log_full_1_11_2017_modem_status
> (cost=0.00..10.59 rows=1 width=0)
> Filter: ((end_date >=
> to_date('2017/12/03'::text, 'YY/MM/DD'::text)) AND (end_date <= to_date(
> '2017/12/04'::text, 'YY/MM/DD'::text)))
>
> and so on parallel seq for each partition that I have..
>
>
> 2018-02-04 15:29 GMT+02:00 legrand legrand <legrand_legrand(at)hotmail(dot)com>:
>
>> Explain analyse
>> Output ?
>>
>>
>>
>> --
>> Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f20
>> 50081.html
>>
>>
>