Re: postgresql 10.1 wrong plan in when using partitions bug

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.cherkassky@xxxxxxxxx>:
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@xxxxxxxxxxx>:
Explain analyse
Output ?


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux