[PERFORM] 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 usi

Re: [PERFORM] Fast insert, but slow join and updates for table with 4 billion rows

Lars Aksel Opsahl writes: > In this example I have two tables one with 4 billion rows and another with > 5 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) This particular query w

Re: [PERFORM] Fast insert, but slow join and updates for table with 4 billion rows

Hi Yes this makes both the update and both selects much faster. We are now down to 3000 ms. for select, but then I get a problem with another SQL where I only use epoch in the query. SELECT count(o.*) FROM met_vaer_wisline.nora_bc25_observation o WHERE o.epoch = 128844; count ---

Re: [PERFORM] Fast insert, but slow join and updates for table with 4 billion rows

On Mon, Oct 24, 2016 at 2:07 PM, Lars Aksel Opsahl wrote: > Hi > > Yes this makes both the update and both selects much faster. We are now down > to 3000 ms. for select, but then I get a problem with another SQL where I > only use epoch in the query. > > SELECT count(o.*) FROM met_vaer_wisline.

Re: [PERFORM] Fast insert, but slow join and updates for table with 4 billion rows

Hi Yes that helps, I tested this on now on the first column now. This basically means that only the first column in multiple column index may be used in single column query. EXPLAIN analyze SELECT count(o.*) FROM met_vaer_wisline.nora_bc25_observation o WHERE o.point_uid_ref = 15 ;