Re: pg12 partitions show bad performance vs pg96

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

 



I tried to do even something simpler, run the query with only the partition column in the where clause and the results werent good for pg12 : 
PG12 : 
postgres=# explain analyze select * from iot_data where device=51;
                                                              QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..514086.40 rows=1027284 width=37) (actual time=6.777..61558.272 rows=1010315 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on iot_data_0  (cost=0.00..410358.00 rows=428035 width=37) (actual time=1.152..61414.483 rows=336772 loops=3)
         Filter: (device = 51)
         Rows Removed by Filter: 9764341
 Planning Time: 15.720 ms
 Execution Time: 61617.851 ms
(8 rows)



PG9.6
postgres=# explain analyze select * from iot_data where device=51;
                                                       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
 Seq Scan on iot_data  (cost=0.00..2083334.60 rows=976667 width=37) (actual time=21.922..16753.492 rows=1010315 loops=1)
   Filter: (device = 51)
   Rows Removed by Filter: 98989685
 Planning time: 0.119 ms
 Execution time: 16810.787 ms
(5 rows)
 
 Besides hardware, anything else worth checking ? the machine are identical in aspect of resources.

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

  Powered by Linux