Re: query optimization

I see that attribute project is defined as integer in library, and as
varchar(8) in clone. I suspect that's what causing the problem and
forcing a seq scan on library.

On Thu, 2004-03-04 at 14:56, Charles Hauser wrote:

> 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 majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>