Hi, what platform are you working on? what was the response time for the second call? (run query twice...) On 15/03/14 22:33, Summer Xia wrote:
Hi all, I created a table with 10GB (60M records), added unique index (hidden_id) manually after data insertion. I had the simplest query but it took one minutes to complete. select hidden_id from netflow where hidden_id = 350000; And also the query took tens of minutes "select * from netflow order by hidden_id limit 12500 offset 212500;". It really confuses me.
I post the analysis of the first below. Any clue why it's so slow? trace select hidden_id from netflow where hidden_id = 350000; +----------+------------------------------------------------------------------+ | ticks | stmt | +==========+==================================================================+ | 3 | X_3 := sql.mvc(); | | 15 | X_7=
[69396995] := sql.bind(X_3=0,"sys","netflow","hidd | : : en_id",0); : | 227 | X_4:bat[:oid,:oid] = [69396995] := sql.tid(X_3=0,"sys" | : : ,"netflow"); : | 72978741 | X_36= [1] := algebra.subselect(X_7= [69396995] | : : ,X_4= :bat[:oid,:oid][69396995],A0=350000:lng,A0=35000 : : : 0:lng,true,true,false); : | 17 | (X_10= [0],r1_10= [0]) := sql.bind(X_3=0,"sys" | : : ,"netflow","hidden_id",2); : | 14 | X_37= [0] := algebra.subselect(r1_10= [0],A0= | : : 350000:lng,A0=350000:lng,true,true,false); : | 6 | X_13= [0] := sql.bind(X_3=0,"sys","netflow","hidden_id" | : : ,1); : | 15 | X_38= [0] := algebra.subselect(X_13= [0],X_4= | : : :bat[:oid,:oid][69396995],A0=350000:lng,A0=350000:lng : : : ,true,true,false); : | 4 | X_15= [1] := sql.subdelta(X_36= [1],X_4= :bat[:oid,:oid][69396995],X_10= [0],X_37= [0],X_38= [0]); : | 20 | X_17= [1] := sql.projectdelta(X_15= [1],X_7=< | : : tmp_2510>[69396995],X_10= [0],r1_10= [0],X_13= : : : [0]); : | 6 | X_18 := sql.resultSet(1,1,X_17= [1]); | | 7 | sql.rsColumn(X_18=1,"sys.netflow","hidden_id","bigint",64,0,X_17 | : : = [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 );
Best regards, Summer
_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list