Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1byaOA-0005kD-Lo for pgsql-performance@arkaria.postgresql.org; Mon, 24 Oct 2016 08:13:46 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1byaO9-0002oN-Fi for pgsql-performance@arkaria.postgresql.org; Mon, 24 Oct 2016 08:13:45 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1byaMS-0000pI-Sr for pgsql-performance@postgresql.org; Mon, 24 Oct 2016 08:12:01 +0000 Received: from symbm03.itasdata.no ([128.39.191.25]) by makus.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1byaMM-0002S4-Vl for pgsql-performance@postgresql.org; Mon, 24 Oct 2016 08:11:59 +0000 X-AuditID: 8027bf19-55dff70000004339-bc-580dc2451c8e Received: from nibioexch.nibio.no (pf-18457.bioforsk.no [128.39.184.57]) by symbm03.itasdata.no (Symantec Messaging Gateway) with SMTP id 45.E9.17209.542CD085; Mon, 24 Oct 2016 10:11:52 +0200 (CEST) Received: from NIBIOEXCH01.int.nibio.no (10.100.32.20) by NIBIOEXCH02.int.nibio.no (10.100.32.21) with Microsoft SMTP Server (TLS) id 15.1.225.42; Mon, 24 Oct 2016 10:11:48 +0200 Received: from NIBIOEXCH01.int.nibio.no ([10.100.32.20]) by NIBIOEXCH01.int.nibio.no ([10.100.32.20]) with mapi id 15.01.0225.041; Mon, 24 Oct 2016 10:11:48 +0200 From: Lars Aksel Opsahl To: "pgsql-performance@postgresql.org" Subject: Fast insert, but slow join and updates for table with 4 billion rows Thread-Topic: Fast insert, but slow join and updates for table with 4 billion rows Thread-Index: AQHSLc5ExwlyuwQD1U6YnaijFY0RCA== Date: Mon, 24 Oct 2016 08:11:48 +0000 Message-ID: References: ,<58dff6f9c2e64a8bb82a990ea1910d32@nibio.no> In-Reply-To: <58dff6f9c2e64a8bb82a990ea1910d32@nibio.no> Accept-Language: nb-NO, en-US Content-Language: nb-NO X-MS-Has-Attach: X-MS-TNEF-Correlator: x-ms-exchange-messagesentrepresentingtype: 1 x-originating-ip: [10.100.44.5] Content-Type: multipart/alternative; boundary="_000_ecea7a5ae84345d2bbe12bb9e3f1edf6nibiono_" MIME-Version: 1.0 X-Brightmail-Tracker: H4sIAAAAAAAAA+NgFprGIsWRmVeSWpSXmKPExsXSoL7DUtfjEG+EwZLNShbXZ55jdmD0uLBv GnsAYxSXTUpqTmZZapG+XQJXxvdfp1kLWk4zV2yec4yxgfHKHOYuRk4OCQETieYV+4FsLg4h gROMEqv69rBBONsYJfYs/gnlLGOUOPplEztIC5uAnsT7fU1gtoiAp8Ts/dtYQGxhAV+J3ssX WSHiIRLPJtyFqtGTuHP2OFgNi4CqxOzTn4HWcXDwCphLLH/uDRIWEkiW+PNlEdhFnAIWEgtm zWQFKWEUkJWY28QLEmYWEJeYcfQOC8TRAhJL9pyHekBU4uXjf6wQto7E2etPGCFseYljk34x QvTGS5xb1Ah2Da+AoMTJmU9YJjCKzkIydhaSsllIyiDiehI3pk5hg7C1JZYtfM0MYetKzPh3 iAVZfAEj+ypG4eLK3KRcA2O9zJLE4pTEkkS9vPxNjODY2i+5g/HbdZdDjAIcjEo8vCcu8UQI sSaWATUcYpTgYFYS4b19gDdCiDclsbIqtSg/vqg0J7X4EKM0B4uSOO+zA81hQgLpiSWp2amp BalFMFkmDk6pBkbO5O5DW3Vcnqi2y9xSeHlJ0uDd109C3W9bD8W8ul+8cEb/grmeL7YbPGZv adp/9Y9qEvf3ry5np4bERF9xeCTrqtDtLXGUobZI6hSjyBNjnksTs3fzvBU6u//EMqndz7rT f7sYb+martgSlfExXz6Y4b3HtZBpsYf3pOhrCeUFfJrQcU/9sIESS3FGoqEWc1FxIgDCQ/wX qQIAAA== X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --_000_ecea7a5ae84345d2bbe12bb9e3f1edf6nibiono_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi I have two main problems and that is slow updates and joins, but when I bui= ld up the table met_vaer_wisline.nora_bc25_observation with more than 4 bil= lion 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 t= his 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 tab= les and this takes 397391 ms. with this SQL (the query plan is added is fu= rther down) SELECT o.* FROM met_vaer_wisline.nora_bc25_observation o, met_vaer_wisline.new_data n WHERE o.point_uid_ref =3D n.id_point AND o.epoch =3D n.epoch but if I use this SQL it takes 25727 ms (the query plan is added is furthe= r 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_da= ta) AS n WHERE n.epoch =3D o.epoch ) AND EXISTS (SELECT 1 FROM (SELECT distinct id_point FROM met_vaer_wisline.new= _data) AS n WHERE n.id_point =3D o.point_uid_ref ) ) AS o, met_vaer_wisline.new_data n WHERE o.point_uid_ref =3D n.id_point AND o.epoch =3D 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 t= hat 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 =3D n.id_point AND o.epoch =3D n.epoch -[ RECORD 1 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --- QUERY PLAN | Merge Join (cost=3D0.87..34374722.51 rows=3D52579 width=3D16)= (actual time=3D0.127..397379.844 rows=3D50000 loops=3D1) -[ RECORD 2 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --- QUERY PLAN | Merge Cond: (n.id_point =3D o.point_uid_ref) -[ RECORD 3 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --- QUERY PLAN | Join Filter: (o.epoch =3D 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=3D0.29..23802.89 rows=3D50000 width=3D8) (actual time= =3D0.024..16.736 rows=3D50000 loops=3D1) -[ RECORD 6 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --- QUERY PLAN | -> Index Scan using idx_met_vaer_wisline_nora_bc25_observat= ion_point_uid_ref on nora_bc25_observation o (cost=3D0.58..2927642364.25 r= ows=3D4263866624 width=3D16) (actual time=3D0.016..210486.136 rows=3D217920= 0001 loops=3D1) -[ 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_da= ta) AS n WHERE n.epoch =3D o.epoch ) AND EXISTS (SELECT 1 FROM (SELECT distinct id_point FROM met_vaer_wisline.new= _data) AS n WHERE n.id_point =3D o.point_uid_ref ) ) AS o, met_vaer_wisline.new_data n WHERE o.point_uid_ref =3D n.id_point AND o.epoch =3D n.epoch -[ RECORD 1 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | Hash Semi Join (cost=3D1019.70..1039762.81 rows=3D54862 width= =3D16) (actual time=3D359.284..25717.838 rows=3D50096 loops=3D1) -[ RECORD 2 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | Hash Cond: (o.point_uid_ref =3D new_data_1.id_point) -[ RECORD 3 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | -> Nested Loop (cost=3D0.87..972602.28 rows=3D24964326 wid= th=3D16) (actual time=3D0.287..24412.088 rows=3D24262088 loops=3D1) -[ RECORD 4 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | -> Unique (cost=3D0.29..1014.29 rows=3D248 width=3D4= ) (actual time=3D0.117..6.849 rows=3D248 loops=3D1) -[ RECORD 5 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | -> Index Only Scan using idx_met_vaer_wisline_n= ew_data_epoch on new_data (cost=3D0.29..889.29 rows=3D50000 width=3D4) (ac= tual time=3D0.115..4.521 rows=3D50000 loops=3D1) -[ RECORD 6 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | Heap Fetches: 0 -[ RECORD 7 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | -> Index Scan using idx_met_vaer_wisline_nora_bc25_ob= servation_epoch on nora_bc25_observation o (cost=3D0.58..2911.05 rows=3D10= 0663 width=3D16) (actual time=3D0.014..89.512 rows=3D97831 loops=3D248) -[ RECORD 8 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | Index Cond: (epoch =3D new_data.epoch) -[ RECORD 9 ]--------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | -> Hash (cost=3D1016.31..1016.31 rows=3D202 width=3D4) (ac= tual time=3D16.636..16.636 rows=3D202 loops=3D1) -[ RECORD 10 ]-------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | Buckets: 1024 Batches: 1 Memory Usage: 8kB -[ RECORD 11 ]-------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | -> Unique (cost=3D0.29..1014.29 rows=3D202 width=3D4= ) (actual time=3D0.046..16.544 rows=3D202 loops=3D1) -[ RECORD 12 ]-------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------- QUERY PLAN | -> Index Only Scan using idx_met_vaer_wisline_n= ew_data_id_point on new_data new_data_1 (cost=3D0.29..889.29 rows=3D50000 = width=3D4) (actual time=3D0.046..11.315 rows=3D50000 loops=3D1) -[ 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 --_000_ecea7a5ae84345d2bbe12bb9e3f1edf6nibiono_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable


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 mor= e than 4 billion we are able to insert about 85.000 rows pr sekun= d so thats ok.


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


In this example I have two tables one with 4 billion rows and ano= ther with 50000 rows and then I try to do a standard simple join betwe= en 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 =3D n.id_point <= span style=3D"color:#931a68"> AND o.epoch =3D 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_wi= sline.nora_bc25_observation o

WHERE <= /p>

EXISTS (SELECT 1 FROM  (SELECT distinct epoch FROM  met_vaer_wisline.new_data) AS n WHE= RE n.epoch =3D o.epoch )

AND 

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

) AS o,

met_vaer_wisline.new_data n

WHERE o.point_uid_ref =3D n.id_point <= span style=3D"color:#931a68"> AND o.epoch =3D n.epoch


The columns= are indexed and I did run vacuum analyze on both tables before I tested. w= ork_mem is 200MB but I also tested with much more work_mem but that does&nb= sp;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 <= span style=3D"font-size: 12pt;">?

https://www.postgresq= l.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 =3D n.id_point <= span style=3D"color:#931a68"> AND o.epoch =3D n.epoch



-[ RECOR= D 1 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------

QUERY PLAN | Merge Join  (cost=3D0.87..34374722.51 rows=3D52579 width=3D16) (actual time=3D0.127..397379.844 rows=3D50000 loops=3D1)

-[ RECOR= D 2 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------

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

-[ RECOR= D 3 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------

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

-[ RECOR= D 4 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------

QUERY PLAN |   Rows Removed by Jo= in Filter: 2179150000

-[ RECOR= D 5 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------

QUERY PLAN |   ->  Index Scan us= ing idx_met_vaer_wisline_new_data_id_point on new_data n  (cost=3D0.29..2380= 2.89 rows=3D50000 width=3D8) (actual time=3D0.024..16.736 rows=3D50000 loops=3D1)

-[ RECOR= D 6 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------

QUERY PLAN |   ->  Index Scan us= ing idx_met_vaer_wisline_nora_bc25_observation_point_uid_ref on nora_bc25_observation o  (cost= =3D0.58..2927642364.25 rows=3D4263866624 width=3D16) (actual = time=3D0.016..210486.136 rows= =3D2179200001 loops=3D1)

-[ RECOR= D 7 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------

QUERY PLAN | Total runtime: 397383.663 ms


Time: 397391.388 ms



EXPLAIN  analyze

SELECT 

o.*

FROM 

(

SELECT o.*

FROM 

met_vaer_wi= sline.nora_bc25_observation o

WHERE <= /p>

EXISTS (SELECT 1 FROM  (SELECT distinct epoch FROM  met_vaer_wisline.new_data) AS n WHE= RE n.epoch =3D o.epoch )

AND 

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

) AS o,

met_vaer_wisline.new_data n

WHERE o.point_uid_ref =3D n.id_point <= span style=3D"color:#931a68"> AND o.epoch =3D n.epoch


-[ RECOR= D 1 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN | Hash Semi Join  (cost=3D1019.70..1039762.81 rows=3D54862 width=3D16) (actual time<= /span>=3D359.284..25717.838 rows=3D50096 loops=3D1)

-[ RECOR= D 2 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

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

-[ RECOR= D 3 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN |   ->  Nested Loop  (cost=3D0.87..972602.28 <= span style=3D"color:#931a68"> rows=3D24964326 width=3D16) (actual ti= me=3D0.287..24412.088 rows=3D24262088 loops=3D1)

-[ RECOR= D 4 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN |         ->  Unique  (cost=3D0= .29..1014.29 rows=3D248 width=3D4) (actual time=3D0.117..6.849 rows=3D248 loops=3D1)

-[ RECOR= D 5 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN |               ->  I= ndex Only Scan using idx_met_vaer_wisl= ine_new_data_epoch on new_data  (cost=3D0.29..889.29= rows=3D50000 width=3D4) (actual time=3D0.115..4.521 rows=3D50000 loops=3D1)

-[ RECOR= D 6 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN |                  =   Heap Fetches: 0

-[ RECOR= D 7 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN |         ->  Index Scan using idx_met_vaer_wisline_nora_bc25_observation_= epoch on nora_bc25_observation o  (cost= =3D0.58..2911.05 rows=3D100663 width=3D16) (actual time=3D0.014..89.512 rows=3D978= 31 loops=3D248)

-[ RECOR= D 8 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

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

-[ RECOR= D 9 ]--------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN |   ->  Hash  (cost=3D1016.31..1016.31 rows=3D202 width=3D4) (actual time=3D16.636..16.636 rows=3D202 loops=3D1)

-[ RECOR= D 10 ]-------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN |         Buckets: 1024  Batches: 1&nbs= p; Memory Usage: 8kB

-[ RECOR= D 11 ]-------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN |         ->  Unique  (cost=3D0= .29..1014.29 rows=3D202 width=3D4) (actual time=3D0.046..16.544 rows=3D202 loops=3D1)

-[ RECOR= D 12 ]-------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN |               ->  I= ndex Only Scan using idx_met_vaer_wisl= ine_new_data_id_point on new_data new_data_1  (cost=3D0= .29..889.29 rows=3D50000 width=3D4) (actual time=3D0.046..11.315 rows=3D50000 loops=3D1)

-[ RECOR= D 13 ]-------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN |                  =   Heap Fetches: 0

-[ RECOR= D 14 ]-------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------

QUERY PLAN | Total runtime: 25719.120 ms


Time: 25727.097 ms



select version();

                    =                     &nbs= p;         version           &= nbsp;                    =                    

---------------------------------------------------------------------------= -----------------------------------

 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      | int= eger | not null

 epoch              | integer | not null

 windspeed_10m      | rea= l    | 

 air_temperature_2m | real  =   | 

Indexes:

    "idx_met_vaer_wisli= ne_nora_bc25_observation_epoch" btree (e= poch)

    "idx_met_vaer_wisli= ne_nora_bc25_observation_point_uid_ref" = btree (point_uid_ref)

    

\d met_vaer_wisline.new_data ;

     Unlogged table "met_vaer_wisline.new_data"

       Column       |     &nbs= p; Type        | Modifiers 

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

 windspeed_10m      | rea= l              | 

 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_wisli= ne_new_data_epoch" btree (epoch)<= /p>

    "idx_met_vaer_wisli= ne_new_data_id_point" btree (id_point)


vacuum analyze met_vaer_wisline.nora_bc25_observation;


vacuum analyze met_vaer_wisline.new_data;


SELECT c= ount(*) from met_vaer_wisline.new_data;

 count 

-------

 50000

(1 row)


SELECT c= ount(*) from met_vaer_wisline.nora_bc25_observ= ation ;

   count    

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

  4263866304


Thanks .


Lars



--_000_ecea7a5ae84345d2bbe12bb9e3f1edf6nibiono_--