All,
I have the following query which is running quite slow on our server and
was hoping someone would have suggestions how I might improve it.
est3=>EXPLAIN SELECT clone.uniquename,clone.name,library.type,clone.clone_id
est3-> FROM library,clone_aceg
est3-> JOIN clone USING (clone_id)
est3-> WHERE clone_aceg.aceg_id = 8 AND
est3-> clone.project=library.project;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..27.92 rows=1 width=57)
Join Filter: (("outer".project)::text = ("inner".project)::text)
-> Nested Loop (cost=0.00..18.55 rows=4 width=43)
-> Index Scan using aceg_id_clone_aceg_key on clone_aceg (cost=0.00..3.05
rows=4 width=4)
Index Cond: (aceg_id = 8)
-> Index Scan using clone_pkey on clone (cost=0.00..3.91 rows=1 width=39)
Index Cond: ("outer".clone_id = clone.clone_id)
-> Seq Scan on library (cost=0.00..2.15 rows=15 width=14)
(8 rows)
relevant tables below.
regards,
Charles
Tables:
Table "public.clone"
Column | Type | Modifiers
------------+-----------------------+--------------------------------------------------------
clone_id | integer | not null default
nextval('"clone_clone_id_seq"'::text)
name | character varying(10) | not null
uniquename | text | not null
project | character varying(8) |
p_end | character varying(2) |
lib_id | integer |
accn | character varying(10) |
seq | text | not null
seqlen | integer |
hq_start | integer |
hq_end | integer |
scaffold | character varying(50) |
Indexes: clone_pkey primary key btree (clone_id),
clone_uniquename_idx unique btree (uniquename),
clone_accn_idx btree (accn),
clone_name_idx btree (name),
clone_project_idx btree (project),
clone_scaf_idx btree (scaffold)
Table "public.library"
Column | Type | Modifiers
-------------+---------+--------------------------------------------------------
lib_id | integer | not null default nextval('"library_lib_id_seq"'::text)
source | text |
type | text |
project | integer |
name | text |
organism | text |
strain | text |
vector | text |
rs1 | text |
rs2 | text |
preparation | text |
Indexes: library_pkey primary key btree (lib_id),
library_project_idx btree (project),
library_type_idx btree ("type")
Table "public.clone_aceg"
Column | Type | Modifiers
----------+---------+-----------
clone_id | integer |
aceg_id | integer |
Indexes: clone_aceg_clone_id_key unique btree (clone_id, aceg_id),
aceg_id_clone_aceg_key btree (aceg_id),
clone_id_clone_aceg_key btree (clone_id)
Foreign Key constraints: cloneid FOREIGN KEY (clone_id) REFERENCES clone(clone_id) ON
UPDATE NO ACTION ON DELETE CASCADE,
acegid FOREIGN KEY (aceg_id) REFERENCES aceg(aceg_id) ON
UPDATE NO ACTION ON DELETE CASCADE
List of relations
Schema | Name | Type | Owner | Table
--------+----------------------------+-------+---------+--------------
public | aceg_aceg_idx | index | chauser | aceg
public | aceg_assembly_key | index | chauser | aceg
public | aceg_blast_aceg_id_key | index | chauser | aceg_blast
public | aceg_contig_idx | index | chauser | aceg
public | aceg_g_scaffold_idx | index | chauser | aceg
public | aceg_has_blast_idx | index | chauser | aceg
public | aceg_id_aceg_blast_key | index | chauser | aceg_blast
public | aceg_id_clone_aceg_key | index | chauser | clone_aceg
public | aceg_pkey | index | chauser | aceg
public | aceg_uniquename_idx | index | chauser | aceg
public | blast_id_aceg_blast_key | index | chauser | aceg_blast
public | blast_id_contig_blast_key | index | chauser | contig_blast
public | blast_ortho_idx | index | chauser | blast
public | blast_pkey | index | chauser | blast
public | clone_accn_idx | index | chauser | clone
public | clone_aceg_clone_id_key | index | chauser | clone_aceg
public | clone_contig_clone_id_key | index | chauser | clone_contig
public | clone_id_clone_aceg_key | index | chauser | clone_aceg
public | clone_id_clone_contig_key | index | chauser | clone_contig
public | clone_name_idx | index | chauser | clone
public | clone_pkey | index | chauser | clone
public | clone_project_idx | index | chauser | clone
public | clone_scaf_idx | index | chauser | clone
public | clone_uniquename_idx | index | chauser | clone
public | contig_ace_idx | index | chauser | contig
public | contig_assembly_idx | index | chauser | contig
public | contig_assembly_key | index | chauser | contig
public | contig_blast_blast_id_key | index | chauser | contig_blast
public | contig_contig_idx | index | chauser | contig
public | contig_has_blast_idx | index | chauser | contig
public | contig_id_clone_contig_key | index | chauser | clone_contig
public | contig_id_contig_blast_key | index | chauser | contig_blast
public | contig_pkey | index | chauser | contig
public | contig_uniquename_idx | index | chauser | contig
public | library_pkey | index | chauser | library
public | library_project_idx | index | chauser | library
public | library_type_idx | index | chauser | library
(
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
