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

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