CREATE TABLE "proteins" ( "name" character varying(16), "organism" text, "start_position" int4, "last_position" int4, "seq" text, "purpose" text, "alternate_key" character varying(16), "comment" text, "compared" bool, "complement" bool, "chromosome" character varying(4), "essentiality" float8); QUERY: CREATE TABLE "proteins" ( "name" character varying(16), "organism" text, "start_position" int4, "last_position" int4, "seq" text, "purpose" text, "alternate_key" character varying(16), "comment" text, "compared" bool, "complement" bool, "chromosome" character varying(4), "essentiality" float8); COPY "proteins" FROM stdin; QUERY: COPY "proteins" FROM stdin; CREATE INDEX "protein_names" on "proteins" using btree ( "name" "varchar_ops" ); QUERY: CREATE INDEX "protein_names" on "proteins" using btree ( "name" "varchar_ops" ); CREATE INDEX "protein_organism" on "proteins" using btree ( "organism" "text_ops" ); QUERY: CREATE INDEX "protein_organism" on "proteins" using btree ( "organism" "text_ops" ); CREATE CREATE CREATE EOF real 1:11.42 user 3.15 sys 0.53 CREATE TABLE "comparisons_4" ( "name1" character varying(16), "name2" character varying(16), "z_score" float8, "expected" float8, "local_overlap_ratio" float8, "local_overlap_count" int4, "overlap_ratio" float8, "code" int4); QUERY: CREATE TABLE "comparisons_4" ( "name1" character varying(16), "name2" character varying(16), "z_score" float8, "expected" float8, "local_overlap_ratio" float8, "local_overlap_count" int4, "overlap_ratio" float8, "code" int4); COPY "comparisons_4" FROM stdin; QUERY: COPY "comparisons_4" FROM stdin; CREATE INDEX "comparisons_4_name1" on "comparisons_4" using btree ( "name1" "varchar_ops" ); QUERY: CREATE INDEX "comparisons_4_name1" on "comparisons_4" using btree ( "name1" "varchar_ops" ); CREATE INDEX "comparisons_4_name2" on "comparisons_4" using btree ( "name2" "varchar_ops" ); QUERY: CREATE INDEX "comparisons_4_name2" on "comparisons_4" using btree ( "name2" "varchar_ops" ); CREATE INDEX "comparisons_4_code" on "comparisons_4" using btree ( "code" "int4_ops" ); QUERY: CREATE INDEX "comparisons_4_code" on "comparisons_4" using btree ( "code" "int4_ops" ); CREATE CREATE CREATE CREATE EOF real 16:42.13 user 5.86 sys 0.96 CREATE TABLE "concordance_91" ( "target_name" character varying(16), "matched_name" character varying(16), "score" text); QUERY: CREATE TABLE "concordance_91" ( "target_name" character varying(16), "matched_name" character varying(16), "score" text); REVOKE ALL on "concordance_91" from PUBLIC; QUERY: REVOKE ALL on "concordance_91" from PUBLIC; GRANT ALL on "concordance_91" to PUBLIC; QUERY: GRANT ALL on "concordance_91" to PUBLIC; COPY "concordance_91" FROM stdin; QUERY: COPY "concordance_91" FROM stdin; CREATE CHANGE CHANGE EOF real 0.30 user 0.02 sys 0.04 explain select * from comparisons_4 where name1 = 'HI0001'; QUERY: explain select * from comparisons_4 where name1 = 'HI0001'; NOTICE: QUERY PLAN: Index Scan using comparisons_4_name1 on comparisons_4 (cost=2.05 rows=1 width=64) explain select count(*) from comparisons_4 where code = 80003; QUERY: explain select count(*) from comparisons_4 where code = 80003; NOTICE: QUERY PLAN: Aggregate (cost=2.05 rows=1 width=12) -> Index Scan using comparisons_4_code on comparisons_4 (cost=2.05 rows=1 width=12) explain select p.name, p.purpose from comparisons_4 c, proteins p where c.name1 = 'HI0003' and c.name2 = p.name; QUERY: explain select p.name, p.purpose from comparisons_4 c, proteins p where c.name1 = 'HI0003' and c.name2 = p.name; NOTICE: QUERY PLAN: Nested Loop (cost=4.10 rows=1 width=36) -> Index Scan using comparisons_4_name1 on comparisons_4 c (cost=2.05 rows=1 width=12) -> Index Scan using protein_names on proteins p (cost=2.05 rows=36840 width=24) explain select c.target_name, c.matched_name, c.score, p.purpose from concordance_91 c, proteins p where c.matched_name = p.name; QUERY: explain select c.target_name, c.matched_name, c.score, p.purpose from concordance_91 c, proteins p where c.matched_name = p.name; NOTICE: QUERY PLAN: Nested Loop (cost=2093.00 rows=36840 width=60) -> Seq Scan on concordance_91 c (cost=43.00 rows=1000 width=36) -> Index Scan using protein_names on proteins p (cost=2.05 rows=36840 width=24) EXPLAIN EXPLAIN EXPLAIN EXPLAIN EOF QUERY: select * from comparisons_4 where name1 = 'HI0001' name1 |name2 |z_score|expected|local_overlap_ratio|local_overlap_count|overlap_ratio| code ------+---------------+-------+--------+-------------------+-------------------+-------------+----- HI0001|PDB2DBV_O | 1217.4| 0| 0.56716| 335| 0.560468|30012 HI0001|PDB4DBV_O | 1207| 0| 0.56418| 335| 0.557523|30012 HI0001|PDB2GD1_P | 1226.4| 0| 0.57015| 335| 0.563423|30012 HI0001|PDB1GAE_O | 1861.8| 0| 0.83133| 332| 0.814164|30012 HI0001|PDB4GPD_1 | 1357.8| 0| 0.64865| 333| 0.637169|30012 HI0001|HP1346 | 850.3| 6.9e-41| 0.39222| 334| 0.386435|30005 HI0001|TP0844 | 780.3| 5.8e-37| 0.46307| 352| 0.465716|30014 HI0001|PDB1HDG_O | 1020.4| 0| 0.48024| 329| 0.466074|30012 HI0001|SCPIR-DEBYG1 | 1405.2| 0| 0.6497| 334| 0.640117|30000 HI0001|Rv1436 | 970.4| 0| 0.49558| 339| 0.49558|30010 HI0001|PDB1CER_O | 949.7| 0| 0.47734| 331| 0.466075|30012 HI0001|PDB1NLH_ | 935.1| 0| 0.46847| 333| 0.458825|30012 HI0001|PDB1GGA_A | 918| 0| 0.52125| 353| 0.51397|30012 HI0001|PDB1GAD_O | 1869.5| 0| 0.83434| 332| 0.817112|30012 HI0001|PDB1GYP_A | 900.1| 0| 0.51275| 353| 0.505589|30012 HI0001|MG301 | 866.7| 0| 0.43155| 336| 0.427731|30004 HI0001|SCSW-G3P1_YEAST| 1425.3| 0| 0.65868| 334| 0.648965|30000 HI0001|ScTDH1 | 1424.6| 0| 0.65868| 334| 0.648965|30013 HI0001|ScTDH2 | 1405.2| 0| 0.6497| 334| 0.640117|30013 HI0001|SCSW-G3P3_YEAST| 1417.5| 0| 0.65868| 334| 0.648965|30000 HI0001|ScTDH3 | 1416.8| 0| 0.65868| 334| 0.648965|30013 HI0001|SCGP-3720 | 1416.8| 0| 0.66168| 334| 0.651921|30000 HI0001|SCGP-E243731 | 1416.8| 0| 0.65868| 334| 0.648965|30000 HI0001|SCSW-G3P2_YEAST| 1405.9| 0| 0.6497| 334| 0.640117|30000 HI0001|SCGP-1008189 | 1424.6| 0| 0.65868| 334| 0.648965|30000 HI0001|SCGP-3726 | 1398.7| 0| 0.6497| 334| 0.640117|30000 HI0001|PDB3GPD_R | 1432.2| 0| 0.63772| 334| 0.628314|30012 HI0001|HP0921 | 762.6| 5.6e-36| 0.40407| 344| 0.41003|30005 HI0001|MJ1146 | 124.7| 1.9| 0.25094| 267| 0.195338|30007 HI0001|SCGP-3724 | 1371.5| 0| 0.63772| 334| 0.628314|30000 (30 rows) real 0.18 user 0.02 sys 0.03 QUERY: select count(*) from comparisons_4 where code = 80003 count ----- 3231 (1 row) real 0.34 user 0.02 sys 0.03 QUERY: select p.name, p.purpose from comparisons_4 c, proteins p where c.name1 = 'HI0003' and c.name2 = p.name name |purpose -------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- MG263 |hypothetical protein HP0652 |phosphoserine phosphatase MJ1594 |phosphoserine phosphatase MG125 |hypothetical protein TP0290 |conserved hypothetical protein HI1033 |phosphoserine phosphatase (o-phosphoserine phosphohydrolase) HI0597 |hypothetical protein Rv3813c|(MTCY409.17), len: 273. Unknown, similar to many hypothetical proteins eg. YXEH_BACSU P54947 hypothetical 30.2 kd protein in idh-deor (270 aa), fasta results; opt: 329 z-score: 456.0 E(): 2.2e-18, 32.2% identity in 267 aa overlap Rv3042c|(MTV012.57c), len: 409. The C-terminal domain (150-409) is highly similar to several SerB proteins e.g. P06862|SERB_ECOLI. N-terminus (1-150) shows no similarity, FASTA score: sp|P06862|SERB_ECOLI PHOSPHOSERINE PHOSPHATASE (EC 3.1 (322 aa) opt: 628 z-score: 753.3 E(): 0; 46.8%identity in 235 aa overlap. TBparse score is 0.884 MG265 |hypothetical protein (10 rows) real 0.24 user 0.02 sys 0.03 QUERY: select c.target_name, c.matched_name, c.score, p.purpose from concordance_91 c, proteins p where c.matched_name = p.name target_name|matched_name| score|purpose -----------+------------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ECinfA |BSInfA |0.680556|initiation factor IF-1 ECinfA |HI0548 | 0.80952|initiation factor IF-1 ECinfA |HP1298 | 0.61111|translation initiation factor EF-1 ECinfA |Rv3462c |0.684936|(MTCY13E12.15c), len: 73 aa. infA. Probable initiation factor IF-1. FASTA results: identical to IF1_MYCBO P45957 initiation factor if-1 (72 aa) ECrpmA |BB0780 |0.635297|ribosomal protein L27 ECrpmA |HI0879 | 0.87059|ribosomal protein L27 ECrpmA |HP0297 |0.613632|ribosomal protein L27 ECrpmA |Rv2441c |0.616278|(MTCY428.05), len: 86. Probable rpmA, similar to eg RL27_ECOLI P02427 50s ribosomal protein l27, (84 aa), fasta scores, opt: 328, E(): 7.1e-17, (64.2% identity in 81 aa overlap); contains PS00831 Ribosomal protein L27 signature (8 rows) real 0.17 user 0.02 sys 0.03