Postgres Pro
Downloads
Home   >   mailing lists

Thread: query optimization

query optimization

From
Charles Hauser
Date:
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.55rows=4 width=43)        ->  Index Scan using aceg_id_clone_aceg_key on clone_aceg  (cost=0.00..3.05
rows=4width=4)              Index Cond: (aceg_id = 8)        ->  Index Scan using clone_pkey on clone  (cost=0.00..3.91
rows=1width=39)              Index Cond: ("outer".clone_id = clone.clone_id)  ->  Seq Scan on library  (cost=0.00..2.15
rows=15width=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
 
(

Re: query optimization

From
Tom Lane
Date:
Charles Hauser <chauser@duke.edu> writes:
> 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.

Have you vacuumed or analyzed these tables recently?  The EXPLAIN
numbers show that the planner thinks all the tables are tiny, which
hardly seems likely given that you're complaining about the speed.

If it's still bad after you VACUUM ANALYZE, send EXPLAIN ANALYZE (not
just EXPLAIN) output and maybe we can give some help.
        regards, tom lane

Re: query optimization

From
"Jonathan M. Gardner"
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thursday 04 March 2004 9:56 am, 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.
>

Might want to try emailing the performance list as well, after you take
Tom's advice. They tend to get more excited about performance issues.

- --
Jonathan Gardner
jgardner@jonathangardner.net
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)

iD8DBQFASKq6qp6r/MVGlwwRAjeiAKCJpD/imnxP71f1GU8zpUVrWvd+qACeNcOF
9KvCM7HSp7jr9Ep9Xrs050M=
=Ph6a
-----END PGP SIGNATURE-----

Re: query optimization

From
Franco Bruno Borghesi
Date:
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>