I created a table with 10GB (60M records), added unique index (hidden_id) manually after data insertion.
And also the query took tens of minutes "select * from netflow order by hidden_id limit 12500 offset 212500;".
I post the analysis of the first below. Any clue why it's so slow?
+----------+------------------------------------------------------------------+
| ticks | stmt |
+==========+==================================================================+
| 3 | X_3 := sql.mvc(); |
| 15 | X_7=<tmp_2510>[69396995] := sql.bind(X_3=0,"sys","netflow","hidd |
: : en_id",0); :
| 227 | X_4:bat[:oid,:oid] =<tmp_13332>[69396995] := sql.tid(X_3=0,"sys" |
: : ,"netflow"); :
| 72978741 | X_36=<tmp_4053>[1] := algebra.subselect(X_7=<tmp_2510>[69396995] |
: : ,X_4=<tmp_13332>:bat[:oid,:oid][69396995],A0=350000:lng,A0=35000 :
: : 0:lng,true,true,false); :
| 17 | (X_10=<tmp_2175>[0],r1_10=<tmp_3416>[0]) := sql.bind(X_3=0,"sys" |
: : ,"netflow","hidden_id",2); :
| 14 | X_37=<tmp_13337>[0] := algebra.subselect(r1_10=<tmp_3416>[0],A0= |
: : 350000:lng,A0=350000:lng,true,true,false); :
| 6 | X_13=<tmp_3416>[0] := sql.bind(X_3=0,"sys","netflow","hidden_id" |
: : ,1); :
| 15 | X_38=<tmp_11053>[0] := algebra.subselect(X_13=<tmp_3416>[0],X_4= |
: : <tmp_13332>:bat[:oid,:oid][69396995],A0=350000:lng,A0=350000:lng :
: : ,true,true,false); :
| 4 | X_15=<tmp_4053>[1] := sql.subdelta(X_36=<tmp_4053>[1],X_4=<tmp_1 |
: : 3332>:bat[:oid,:oid][69396995],X_10=<tmp_2175>[0],X_37=<tmp_1333 :
: : 7>[0],X_38=<tmp_11053>[0]); :
| 20 | X_17=<tmp_11053>[1] := sql.projectdelta(X_15=<tmp_4053>[1],X_7=< |
: : tmp_2510>[69396995],X_10=<tmp_2175>[0],r1_10=<tmp_3416>[0],X_13= :
: : <tmp_3416>[0]); :
| 6 | X_18 := sql.resultSet(1,1,X_17=<tmp_11053>[1]); |
| 7 | sql.rsColumn(X_18=1,"sys.netflow","hidden_id","bigint",64,0,X_17 |
: : =<tmp_11053>[1]); :
| 2 | X_23 := io.stdout(); |
| 25 | sql.exportResult(X_23=="104d2":streams,X_18=1); |
| 1 | end s1_3; |
| 73011629 | X_5:void := user.s1_3(350000:lng); |
+----------+------------------------------------------------------------------+
This is the table being created.
CREATE TABLE "netflow" (
"time_seconds" double DEFAULT NULL,
"parsed_date" timestamp DEFAULT NULL,
"date_time_str" varchar(45) DEFAULT NULL,
"ip_layer_protocol" bigint DEFAULT NULL,
"ip_layer_protocol_code" varchar(45) DEFAULT NULL,
"first_seen_src_ip" varchar(45) DEFAULT NULL,
"first_seen_dest_ip" varchar(45) DEFAULT NULL,
"first_seen_src_port" bigint DEFAULT NULL,
"first_seen_dest_port" bigint DEFAULT NULL,
"more_fragments" varchar(45) DEFAULT NULL,
"cont_fragments" varchar(45) DEFAULT NULL,
"duration_seconds" bigint DEFAULT NULL,
"first_seen_src_payload_bytes" bigint DEFAULT NULL,
"first_seen_dest_payload_bytes" bigint DEFAULT NULL,
"first_seen_src_total_bytes" bigint DEFAULT NULL,
"first_seen_dest_total_bytes" bigint DEFAULT NULL,
"first_seen_src_packet_count" bigint DEFAULT NULL,
"first_seen_dest_packet_count" bigint DEFAULT NULL,
"record_force_out" varchar(45) DEFAULT NULL
);