Обсуждение: Fast insert, but slow join and updates for table with 4 billion rows

Lars Aksel Opsahl <Lars.Opsahl@nibio.no> writes:
> 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
simplejoin between this two tables and this takes 397391  ms. with this SQL (the query plan is added is further down) 

This particular query would work a lot better if you had an index on
nora_bc25_observation (point_uid_ref, epoch), ie both join columns
in one index.  I get the impression that that ought to be the primary
key of the table, which would be an even stronger reason to have a
unique index on it.

            regards, tom lane

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
problemwith another SQL where I only use epoch in the query.  

SELECT count(o.*) FROM  met_vaer_wisline.nora_bc25_observation o WHERE o.epoch = 1288440000;
 count
-------
 97831
(1 row)
Time: 92763.389 ms

To get the SQL above work fast it seems like we also need a single index on the epoch column, this means two indexes on
thesame column and that eats memory when we have more than 4 billion rows. 

Is it any way to avoid to two indexes on the epoch column ?

Thanks.

Lars

EXPLAIN  analyze SELECT count(o.*) FROM  met_vaer_wisline.nora_bc25_observation o WHERE o.epoch = 1288440000;
-[ RECORD 1
]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Aggregate  (cost=44016888.13..44016888.14 rows=1 width=42) (actual time=91307.470..91307.471 rows=1
loops=1)
-[ RECORD 2
]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   ->  Index Scan using idx_met_vaer_wisline_nora_bc25_observation_test on nora_bc25_observation o
(cost=0.58..44016649.38rows=95500 width=42) (actual time=1.942..91287.495 rows=97831 loops=1) 
-[ RECORD 3
]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |         Index Cond: (epoch = 1288440000)
-[ RECORD 4
]-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Total runtime: 91307.534 ms


EXPLAIN  analyze
SELECT count(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 | Aggregate  (cost=131857.71..131857.72 rows=1 width=42) (actual time=182.459..182.459 rows=1 loops=1)
-[ RECORD 2
]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   ->  Nested Loop  (cost=0.58..131727.00 rows=52283 width=42) (actual time=0.114..177.420 rows=50000
loops=1)
-[ RECORD 3
]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |         ->  Seq Scan on new_data n  (cost=0.00..1136.00 rows=50000 width=8) (actual time=0.050..7.873
rows=50000loops=1) 
-[ RECORD 4
]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |         ->  Index Scan using idx_met_vaer_wisline_nora_bc25_observation_test on nora_bc25_observation o
(cost=0.58..2.60rows=1 width=50) (actual time=0.003..0.003 rows=1 loops=50000) 
-[ RECORD 5
]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |               Index Cond: ((point_uid_ref = n.id_point) AND (epoch = n.epoch))
-[ RECORD 6
]----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Total runtime: 182.536 ms

Time: 3095.618 ms


Lars


________________________________________
Fra: pgsql-performance-owner@postgresql.org <pgsql-performance-owner@postgresql.org> på vegne av Tom Lane
<tgl@sss.pgh.pa.us>
Sendt: 24. oktober 2016 14:52
Til: Lars Aksel Opsahl
Kopi: pgsql-performance@postgresql.org
Emne: Re: [PERFORM] Fast insert, but slow join and updates for table with 4 billion rows

Lars Aksel Opsahl <Lars.Opsahl@nibio.no> writes:
> 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
simplejoin between this two tables and this takes 397391  ms. with this SQL (the query plan is added is further down) 

This particular query would work a lot better if you had an index on
nora_bc25_observation (point_uid_ref, epoch), ie both join columns
in one index.  I get the impression that that ought to be the primary
key of the table, which would be an even stronger reason to have a
unique index on it.

                        regards, tom lane


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

On Mon, Oct 24, 2016 at 2:07 PM, Lars Aksel Opsahl <Lars.Opsahl@nibio.no> 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
problemwith another SQL where I only use epoch in the query. 
>
> SELECT count(o.*) FROM  met_vaer_wisline.nora_bc25_observation o WHERE o.epoch = 1288440000;
>  count
> -------
>  97831
> (1 row)
> Time: 92763.389 ms
>
> To get the SQL above work fast it seems like we also need a single index on the epoch column, this means two indexes
onthe same column and that eats memory when we have more than 4 billion rows. 
>
> Is it any way to avoid to two indexes on the epoch column ?

You could try reversing the order. Basically whatever comes first in a
two column index is easier / possible for postgres to use like a
single column index. If not. then you're probably stuck with two
indexes.

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 ;
                                                                               QUERY PLAN
                                                

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=45540.97..45540.98 rows=1 width=42) (actual time=24.715..24.715 rows=1 loops=1)
   ->  Bitmap Heap Scan on nora_bc25_observation o  (cost=477.66..45427.40 rows=45430 width=42) (actual
time=6.436..19.006rows=43832 loops=1) 
         Recheck Cond: (point_uid_ref = 15)
         ->  Bitmap Index Scan on idx_met_vaer_wisline_nora_bc25_observation_test  (cost=0.00..466.30 rows=45430
width=0)(actual time=6.320..6.320 rows=43832 loops=1) 
               Index Cond: (point_uid_ref = 15)
 Total runtime: 24.767 ms
(6 rows)


Thanks

Lars

________________________________________
Fra: Scott Marlowe <scott.marlowe@gmail.com>
Sendt: 24. oktober 2016 22:23
Til: Lars Aksel Opsahl
Kopi: Tom Lane; pgsql-performance@postgresql.org
Emne: 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 <Lars.Opsahl@nibio.no> 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
problemwith another SQL where I only use epoch in the query. 
>
> SELECT count(o.*) FROM  met_vaer_wisline.nora_bc25_observation o WHERE o.epoch = 1288440000;
>  count
> -------
>  97831
> (1 row)
> Time: 92763.389 ms
>
> To get the SQL above work fast it seems like we also need a single index on the epoch column, this means two indexes
onthe same column and that eats memory when we have more than 4 billion rows. 
>
> Is it any way to avoid to two indexes on the epoch column ?

You could try reversing the order. Basically whatever comes first in a
two column index is easier / possible for postgres to use like a
single column index. If not. then you're probably stuck with two
indexes.

Hi

I have now tested through insert and the updated and it works extremely good with out doing any partitioning on big
table(428.812.8392 rows) this case, when we used a common index as Tom Suggested.  

We are able to insert 172.000 rows pr. second.

The number of rows are competed based total time from when we start to read the csv files and until the last file is
done.We use GNU parallel and run 5 threads. The number of inserts are actually 172.000 * 2 because first we copy the
rowsinto a temp table and there we prepare the date and then insert them into the common big main table. There is no
errorsin the log.  

We are able update 98.000 rows pr, second.

Since each update also means one insert we are close 200.000 inserts and updates pr. second. For update we give a
columncolumn that is null a value. Thats is done for all the 4.3 billions rows. We run 5 threads in parallel here also,
andthere is no error and no dead locks.  

To get around the problem with duplication of indexes it's solvable in this project because first we add date and then
wedo analyses, this means that we can have different indexes when adding data and we are using them. 

In this project we going add about 25 billions geo located observations which which will be used for doing analyses. I
supposethat we at some level have to do partitioning but so far Postgres has worked extremely well even if it's based
onMVCC.  

Postgres/Postgis software and communities are sure for sure really fun to work with Postgres/Postgis open source
softwarehold a very high quality.  

Thanks.

Lars

________________________________________
Fra: pgsql-performance-owner@postgresql.org <pgsql-performance-owner@postgresql.org> på vegne av Tom Lane
<tgl@sss.pgh.pa.us>
Sendt: 24. oktober 2016 14:52
Til: Lars Aksel Opsahl
Kopi: pgsql-performance@postgresql.org
Emne: Re: [PERFORM] Fast insert, but slow join and updates for table with 4 billion rows

Lars Aksel Opsahl <Lars.Opsahl@nibio.no> writes:
> 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
simplejoin between this two tables and this takes 397391  ms. with this SQL (the query plan is added is further down) 

This particular query would work a lot better if you had an index on
nora_bc25_observation (point_uid_ref, epoch), ie both join columns
in one index.  I get the impression that that ought to be the primary
key of the table, which would be an even stronger reason to have a
unique index on it.

                        regards, tom lane


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance