Re: query optimization - Mailing list pgsql-sql

I see that attribute project is defined as integer in library, and as varchar(8) in clone. I suspect that's what
causingthe problem and forcing a seq scan on library.<br /><br /> On Thu, 2004-03-04 at 14:56, Charles Hauser wrote:
<blockquotetype="CITE"><pre><font color="#737373"><i>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.92rows=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.05rows=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
onlibrary  (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 nulluniquename
|text                  | not nullproject    | character varying(8)  |p_end      | character varying(2)  |lib_id     |
integer              |accn       | character varying(10) |seq        | text                  | not nullseqlen     |
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_idxbtree (accn),        clone_name_idx btree (name),        clone_project_idx btree (project),
clone_scaf_idxbtree (scaffold) 
                            Table "public.library"  Column    |  Type   |                       Modifiers
-------------+---------+--------------------------------------------------------lib_id      | integer | not null
defaultnextval('"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 relationsSchema |            Name            | Type  |  Owner  |    Table 
--------+----------------------------+-------+---------+--------------public | aceg_aceg_idx              | index |
chauser| acegpublic | aceg_assembly_key          | index | chauser | acegpublic | aceg_blast_aceg_id_key     | index |
chauser| aceg_blastpublic | aceg_contig_idx            | index | chauser | acegpublic | aceg_g_scaffold_idx        |
index| chauser | acegpublic | aceg_has_blast_idx         | index | chauser | acegpublic | aceg_id_aceg_blast_key     |
index| chauser | aceg_blastpublic | aceg_id_clone_aceg_key     | index | chauser | clone_acegpublic | aceg_pkey
        | index | chauser | acegpublic | aceg_uniquename_idx        | index | chauser | acegpublic |
blast_id_aceg_blast_key   | index | chauser | aceg_blastpublic | blast_id_contig_blast_key  | index | chauser |
contig_blastpublic| blast_ortho_idx            | index | chauser | blastpublic | blast_pkey                 | index |
chauser| blastpublic | clone_accn_idx             | index | chauser | clonepublic | clone_aceg_clone_id_key    | index
|chauser | clone_acegpublic | clone_contig_clone_id_key  | index | chauser | clone_contigpublic |
clone_id_clone_aceg_key   | index | chauser | clone_acegpublic | clone_id_clone_contig_key  | index | chauser |
clone_contigpublic| clone_name_idx             | index | chauser | clonepublic | clone_pkey                 | index |
chauser| clonepublic | clone_project_idx          | index | chauser | clonepublic | clone_scaf_idx             | index
|chauser | clonepublic | clone_uniquename_idx       | index | chauser | clonepublic | contig_ace_idx             |
index| chauser | contigpublic | contig_assembly_idx        | index | chauser | contigpublic | contig_assembly_key
| index | chauser | contigpublic | contig_blast_blast_id_key  | index | chauser | contig_blastpublic |
contig_contig_idx         | index | chauser | contigpublic | contig_has_blast_idx       | index | chauser |
contigpublic| contig_id_clone_contig_key | index | chauser | clone_contigpublic | contig_id_contig_blast_key | index |
chauser| contig_blastpublic | contig_pkey                | index | chauser | contigpublic | contig_uniquename_idx
|index | chauser | contigpublic | library_pkey               | index | chauser | librarypublic | library_project_idx
   | index | chauser | librarypublic | library_type_idx           | index | chauser | library 
(




---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate     subscribe-nomail command to
majordomo@postgresql.orgso that your     message can get through to the mailing list cleanly 
</i></font></pre></blockquote>