CREATE DATABASE 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); CREATE COPY "proteins" FROM stdin; CREATE INDEX "protein_names" on "proteins" using btree ( "name" "varchar_ops" ); CREATE CREATE INDEX "protein_organism" on "proteins" using btree ( "organism" "text_ops" ); CREATE real 1:04.49 user 3.14 sys 0.57 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); CREATE COPY "comparisons_4" FROM stdin; CREATE INDEX "comparisons_4_name1" on "comparisons_4" using btree ( "name1" "varchar_ops" ); CREATE CREATE INDEX "comparisons_4_name2" on "comparisons_4" using btree ( "name2" "varchar_ops" ); CREATE CREATE INDEX "comparisons_4_code" on "comparisons_4" using btree ( "code" "int4_ops" ); CREATE real 7:04.43 user 5.87 sys 1.03 CREATE TABLE "concordance_91" ( "target_name" character varying(16), "matched_name" character varying(16), "score" text); CREATE REVOKE ALL on "concordance_91" from PUBLIC; CHANGE GRANT ALL on "concordance_91" to PUBLIC; CHANGE COPY "concordance_91" FROM stdin; real 0.60 user 0.01 sys 0.03 explain select * from comparisons_4 where name1 = 'HI0001'; NOTICE: QUERY PLAN: Seq Scan on comparisons_4 (cost=0.00..15640.81 rows=5918 width=64) EXPLAIN explain select count(*) from comparisons_4 where code = 80003; NOTICE: QUERY PLAN: Aggregate (cost=15655.61..15655.61 rows=1 width=0) -> Seq Scan on comparisons_4 (cost=0.00..15640.81 rows=5918 width=0) EXPLAIN explain select p.name, p.purpose from comparisons_4 c, proteins p where c.name1 = 'HI0003' and c.name2 = p.name; NOTICE: QUERY PLAN: Merge Join (cost=22495.22..23029.70 rows=2180283 width=36) -> Sort (cost=16011.62..16011.62 rows=5918 width=12) -> Seq Scan on comparisons_4 c (cost=0.00..15640.81 rows=5918 width=12) -> Sort (cost=6483.60..6483.60 rows=36840 width=24) -> Seq Scan on proteins p (cost=0.00..3247.40 rows=36840 width=24) EXPLAIN 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: Merge Join (cost=6553.43..7026.43 rows=368400 width=60) -> Sort (cost=69.83..69.83 rows=1000 width=36) -> Seq Scan on concordance_91 c (cost=0.00..20.00 rows=1000 width=36) -> Sort (cost=6483.60..6483.60 rows=36840 width=24) -> Seq Scan on proteins p (cost=0.00..3247.40 rows=36840 width=24) EXPLAIN select * from comparisons_4 where name1 = 'HI0001' name1 | name2 | z_score | expected | local_overlap_ratio | local_overlap_count | overlap_ratio | code --------+-----------------+---------+----------+---------------------+---------------------+---------------+------- HI0001 | PDB1GAD_O | 1869.5 | 0 | 0.83434 | 332 | 0.817112 | 30012 HI0001 | PDB1GAE_O | 1861.8 | 0 | 0.83133 | 332 | 0.814164 | 30012 HI0001 | PDB3GPD_R | 1432.2 | 0 | 0.63772 | 334 | 0.628314 | 30012 HI0001 | SCSW-G3P1_YEAST | 1425.3 | 0 | 0.65868 | 334 | 0.648965 | 30000 HI0001 | SCGP-1008189 | 1424.6 | 0 | 0.65868 | 334 | 0.648965 | 30000 HI0001 | ScTDH1 | 1424.6 | 0 | 0.65868 | 334 | 0.648965 | 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 | ScTDH2 | 1405.2 | 0 | 0.6497 | 334 | 0.640117 | 30013 HI0001 | SCPIR-DEBYG1 | 1405.2 | 0 | 0.6497 | 334 | 0.640117 | 30000 HI0001 | SCGP-3726 | 1398.7 | 0 | 0.6497 | 334 | 0.640117 | 30000 HI0001 | SCGP-3724 | 1371.5 | 0 | 0.63772 | 334 | 0.628314 | 30000 HI0001 | PDB4GPD_1 | 1357.8 | 0 | 0.64865 | 333 | 0.637169 | 30012 HI0001 | PDB2GD1_P | 1226.4 | 0 | 0.57015 | 335 | 0.563423 | 30012 HI0001 | PDB2DBV_O | 1217.4 | 0 | 0.56716 | 335 | 0.560468 | 30012 HI0001 | PDB4DBV_O | 1207 | 0 | 0.56418 | 335 | 0.557523 | 30012 HI0001 | PDB1HDG_O | 1020.4 | 0 | 0.48024 | 329 | 0.466074 | 30012 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 | PDB1GYP_A | 900.1 | 0 | 0.51275 | 353 | 0.505589 | 30012 HI0001 | MG301 | 866.7 | 0 | 0.43155 | 336 | 0.427731 | 30004 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 | HP0921 | 762.6 | 5.6e-36 | 0.40407 | 344 | 0.41003 | 30005 HI0001 | MJ1146 | 124.7 | 1.9 | 0.25094 | 267 | 0.195338 | 30007 (30 rows) real 22.68 user 0.01 sys 0.03 select count(*) from comparisons_4 where code = 80003 count ------- 3231 (1 row) real 21.49 user 0.01 sys 0.03 select p.name, p.purpose from comparisons_4 c, proteins p where c.name1 = 'HI0003' and c.name2 = p.name name | purpose ---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- HI0597 | hypothetical protein HI1033 | phosphoserine phosphatase (o-phosphoserine phosphohydrolase) HP0652 | phosphoserine phosphatase MG125 | hypothetical protein MG263 | hypothetical protein MG265 | hypothetical protein MJ1594 | phosphoserine phosphatase 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 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 TP0290 | conserved hypothetical protein (10 rows) real 23.13 user 0.01 sys 0.03 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 -------------+--------------+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ECrpmA | BB0780 | 0.635297 | ribosomal protein L27 ECinfA | BSInfA | 0.680556 | initiation factor IF-1 ECinfA | HI0548 | 0.80952 | initiation factor IF-1 ECrpmA | HI0879 | 0.87059 | ribosomal protein L27 ECrpmA | HP0297 | 0.613632 | ribosomal protein L27 ECinfA | HP1298 | 0.61111 | translation initiation factor EF-1 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 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) (8 rows) real 11.16 user 0.01 sys 0.03