Fast insert, but slow join and updates for table with 4 billion rows

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

 




Hi


I have two main problems and that is slow updates and joins, but when I build up the table met_vaer_wisline.nora_bc25_observation with more than 4 billion we are able to insert about 85.000 rows pr sekund so thats ok.


The problems start when I need to update or joins with other tables using this table.


In this example I have two tables one with 4 billion rows and another with 50000 rows and then I try to do a standard simple join between this two tables and this takes 397391  ms. with this SQL (the query plan is added is further down)

SELECT o.*

FROM 

met_vaer_wisline.nora_bc25_observation o,

met_vaer_wisline.new_data n

WHERE o.point_uid_ref = n.id_point AND o.epoch = n.epoch


but if I use this SQL  it takes 25727 ms (the query plan is added is further down).

SELECT 

o.*

FROM 

(

SELECT o.*

FROM 

met_vaer_wisline.nora_bc25_observation o

WHERE 

EXISTS (SELECT 1 FROM  (SELECT distinct epoch FROM  met_vaer_wisline.new_data) AS n WHERE n.epoch = o.epoch )

AND 

EXISTS (SELECT 1 FROM  (SELECT distinct id_point FROM  met_vaer_wisline.new_data) AS n WHERE n.id_point = o.point_uid_ref )

) AS o,

met_vaer_wisline.new_data n

WHERE o.point_uid_ref = n.id_point AND o.epoch = n.epoch


The columns are indexed and I did run vacuum analyze on both tables before I tested. work_mem is 200MB but I also tested with much more work_mem but that does not change the execution time.

The CPU goes to 100% when the query is running and there is no IOWait while the SQL is running.


Why is the second SQL 15 times faster ?


Is this normal or have I done something wrong here ?


I have tested clustering around a index but that did not help.


Is the only way to fix slow updates and joins to use partitioning ?

https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html



Here are the SQL and more info


EXPLAIN  analyze

SELECT o.*

FROM 

met_vaer_wisline.nora_bc25_observation o,

met_vaer_wisline.new_data n

WHERE o.point_uid_ref = n.id_point AND o.epoch = n.epoch



-[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN | Merge Join  (cost=0.87..34374722.51 rows=52579 width=16) (actual time=0.127..397379.844 rows=50000 loops=1)

-[ RECORD 2 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |   Merge Cond: (n.id_point = o.point_uid_ref)

-[ RECORD 3 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |   Join Filter: (o.epoch = n.epoch)

-[ RECORD 4 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |   Rows Removed by Join Filter: 2179150000

-[ RECORD 5 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |   ->  Index Scan using idx_met_vaer_wisline_new_data_id_point on new_data n  (cost=0.29..23802.89 rows=50000 width=8) (actual time=0.024..16.736 rows=50000 loops=1)

-[ RECORD 6 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |   ->  Index Scan using idx_met_vaer_wisline_nora_bc25_observation_point_uid_ref on nora_bc25_observation o  (cost=0.58..2927642364.25 rows=4263866624 width=16) (actual time=0.016..210486.136 rows=2179200001 loops=1)

-[ RECORD 7 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN | Total runtime: 397383.663 ms


Time: 397391.388 ms



EXPLAIN  analyze

SELECT 

o.*

FROM 

(

SELECT o.*

FROM 

met_vaer_wisline.nora_bc25_observation o

WHERE 

EXISTS (SELECT 1 FROM  (SELECT distinct epoch FROM  met_vaer_wisline.new_data) AS n WHERE n.epoch = o.epoch )

AND 

EXISTS (SELECT 1 FROM  (SELECT distinct id_point FROM  met_vaer_wisline.new_data) AS n WHERE n.id_point = o.point_uid_ref )

) AS o,

met_vaer_wisline.new_data n

WHERE o.point_uid_ref = n.id_point AND o.epoch = n.epoch


-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN | Hash Semi Join  (cost=1019.70..1039762.81 rows=54862 width=16) (actual time=359.284..25717.838 rows=50096 loops=1)

-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |   Hash Cond: (o.point_uid_ref = new_data_1.id_point)

-[ RECORD 3 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |   ->  Nested Loop  (cost=0.87..972602.28 rows=24964326 width=16) (actual time=0.287..24412.088 rows=24262088 loops=1)

-[ RECORD 4 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |         ->  Unique  (cost=0.29..1014.29 rows=248 width=4) (actual time=0.117..6.849 rows=248 loops=1)

-[ RECORD 5 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |               ->  Index Only Scan using idx_met_vaer_wisline_new_data_epoch on new_data  (cost=0.29..889.29 rows=50000 width=4) (actual time=0.115..4.521 rows=50000 loops=1)

-[ RECORD 6 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |                     Heap Fetches: 0

-[ RECORD 7 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |         ->  Index Scan using idx_met_vaer_wisline_nora_bc25_observation_epoch on nora_bc25_observation o  (cost=0.58..2911.05 rows=100663 width=16) (actual time=0.014..89.512 rows=97831 loops=248)

-[ RECORD 8 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |               Index Cond: (epoch = new_data.epoch)

-[ RECORD 9 ]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |   ->  Hash  (cost=1016.31..1016.31 rows=202 width=4) (actual time=16.636..16.636 rows=202 loops=1)

-[ RECORD 10 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |         Buckets: 1024  Batches: 1  Memory Usage: 8kB

-[ RECORD 11 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |         ->  Unique  (cost=0.29..1014.29 rows=202 width=4) (actual time=0.046..16.544 rows=202 loops=1)

-[ RECORD 12 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |               ->  Index Only Scan using idx_met_vaer_wisline_new_data_id_point on new_data new_data_1  (cost=0.29..889.29 rows=50000 width=4) (actual time=0.046..11.315 rows=50000 loops=1)

-[ RECORD 13 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN |                     Heap Fetches: 0

-[ RECORD 14 ]---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

QUERY PLAN | Total runtime: 25719.120 ms


Time: 25727.097 ms



select version();

                                                   version                                                    

--------------------------------------------------------------------------------------------------------------

 PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

(1 row)


 \d met_vaer_wisline.nora_bc25_observation;

Table "met_vaer_wisline.nora_bc25_observation"

       Column       |  Type   | Modifiers 

--------------------+---------+-----------

 point_uid_ref      | integer | not null

 epoch              | integer | not null

 windspeed_10m      | real    | 

 air_temperature_2m | real    | 

Indexes:

    "idx_met_vaer_wisline_nora_bc25_observation_epoch" btree (epoch)

    "idx_met_vaer_wisline_nora_bc25_observation_point_uid_ref" btree (point_uid_ref)

    

\d met_vaer_wisline.new_data ;

     Unlogged table "met_vaer_wisline.new_data"

       Column       |       Type        | Modifiers 

--------------------+-------------------+-----------

 windspeed_10m      | real              | 

 air_temperature_2m | real              | 

 lon                | character varying | not null

 lat                | character varying | not null

 epoch              | integer           | 

 epoch_as_numeric   | numeric           | not null

 rest               | character varying

 id_point           | integer           | 

Indexes:

    "idx_met_vaer_wisline_new_data_epoch" btree (epoch)

    "idx_met_vaer_wisline_new_data_id_point" btree (id_point)


vacuum analyze met_vaer_wisline.nora_bc25_observation;


vacuum analyze met_vaer_wisline.new_data;


SELECT count(*) from met_vaer_wisline.new_data;

 count 

-------

 50000

(1 row)


SELECT count(*) from met_vaer_wisline.nora_bc25_observation ;

   count    

------------

  4263866304


Thanks .


Lars




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

  Powered by Linux