X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F0450D1B1B3 for ; Thu, 10 Jun 2004 12:45:57 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 99244-03 for ; Thu, 10 Jun 2004 15:45:55 +0000 (GMT) Received: from lucifer.oficina (unknown [200.68.121.201]) by svr1.postgresql.org (Postfix) with ESMTP id 5CBA2D1B1A1 for ; Thu, 10 Jun 2004 12:45:53 -0300 (ADT) Received: from localhost (localhost.akyasociados.com.ar [127.0.0.1]) by lucifer.oficina (8.12.10/8.12.10) with ESMTP id i5AFjnBF013899; Thu, 10 Jun 2004 12:45:49 -0300 (ART) (envelope-from franco@akyasociados.com.ar) Received: from lucifer.oficina ([127.0.0.1]) by localhost (lucifer.oficina [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 11181-10; Thu, 10 Jun 2004 12:45:46 -0300 (ART) Received: from [192.168.1.198] (taz.oficina [192.168.1.198]) (authenticated bits=0) by lucifer.oficina (8.12.10/8.12.10) with ESMTP id i5AFjk2C013892; Thu, 10 Jun 2004 12:45:46 -0300 (ART) (envelope-from franco@akyasociados.com.ar) Subject: Re: query optimization From: Franco Bruno Borghesi To: Charles Hauser Cc: pgsql-sql@postgresql.org In-Reply-To: <1078422984.5001.18.camel@pandorina> References: <1078422984.5001.18.camel@pandorina> Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-Qkft2vSWK0phwGKkBq14" Organization: AK y Asociados S.R.L. Message-Id: <1086882344.1303.2.camel@taz.oficina> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Thu, 10 Jun 2004 12:45:45 -0300 X-Virus-Scanned: by amavisd-new at akyasociados.com.ar X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=HTML_FONTCOLOR_UNSAFE, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200406/90 X-Sequence-Number: 18035 --=-Qkft2vSWK0phwGKkBq14 Content-Type: multipart/alternative; boundary="=-Tw9GQRDU0o0yc+IeKxli" --=-Tw9GQRDU0o0yc+IeKxli Content-Type: text/plain Content-Transfer-Encoding: quoted-printable 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, >=20 > 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. >=20 >=20 > est3=3D>EXPLAIN SELECT clone.uniquename,clone.name,library.type,clone.clo= ne_id > est3-> FROM library,clone_aceg > est3-> JOIN clone USING (clone_id) > est3-> WHERE clone_aceg.aceg_id =3D 8 AND > est3-> clone.project=3Dlibrary.project; > QUERY PLAN > -------------------------------------------------------------------------= ---------------------------- > Nested Loop (cost=3D0.00..27.92 rows=3D1 width=3D57) > Join Filter: (("outer".project)::text =3D ("inner".project)::text) > -> Nested Loop (cost=3D0.00..18.55 rows=3D4 width=3D43) > -> Index Scan using aceg_id_clone_aceg_key on clone_aceg (cost= =3D0.00..3.05 rows=3D4 width=3D4) > Index Cond: (aceg_id =3D 8) > -> Index Scan using clone_pkey on clone (cost=3D0.00..3.91 row= s=3D1 width=3D39) > Index Cond: ("outer".clone_id =3D clone.clone_id) > -> Seq Scan on library (cost=3D0.00..2.15 rows=3D15 width=3D14) > (8 rows) >=20 >=20 >=20 >=20 > relevant tables below. >=20 > regards, >=20 > Charles >=20=20 >=20 > Tables: > Table "public.clone" > Column | Type | Modifiers > ------------+-----------------------+------------------------------------= -------------------- > clone_id | integer | not null default nextval('"clone_cl= one_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) >=20 > 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") >=20 >=20 > 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(ace= g_id) ON UPDATE NO ACTION ON DELETE CASCADE >=20=20 > 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 > (=20 >=20 >=20=20 >=20 >=20 >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >=20 --=-Tw9GQRDU0o0yc+IeKxli Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable I see that attribute project is defined as integer in library, and as varch= ar(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=3D>EXPLAIN SELECT clone.uniquename,clone.name,library.type,clone.cl=
one_id
est3->     FROM library,clone_aceg
est3->     JOIN clone USING (clone_id)
est3->     WHERE clone_aceg.aceg_id =3D 8 AND
est3->     clone.project=3Dlibrary.project;
                                             QUERY PLAN
---------------------------------------------------------------------------=
--------------------------
 Nested Loop  (cost=3D0.00..27.92 rows=3D1 width=3D57)
   Join Filter: (("outer".project)::text =3D ("inner".p=
roject)::text)
   ->  Nested Loop  (cost=3D0.00..18.55 rows=3D4 width=3D43)
         ->  Index Scan using aceg_id_clone_aceg_key on clone_aceg  (cos=
t=3D0.00..3.05 rows=3D4 width=3D4)
               Index Cond: (aceg_id =3D 8)
         ->  Index Scan using clone_pkey on clone  (cost=3D0.00..3.91 ro=
ws=3D1 width=3D39)
               Index Cond: ("outer".clone_id =3D clone.clone_id)
   ->  Seq Scan on library  (cost=3D0.00..2.15 rows=3D15 width=3D14)
(8 rows)




relevant tables below.

regards,

Charles
=20

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(cl=
one_id) ON UPDATE NO ACTION ON DELETE CASCADE,
                         acegid FOREIGN KEY (aceg_id) REFERENCES aceg(aceg_=
id) ON UPDATE NO ACTION ON DELETE CASCADE
=20
                          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
(=20

=20



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to majordomo@postgresql.org so that your
      message can get through to the mailing list cleanly
--=-Tw9GQRDU0o0yc+IeKxli-- --=-Qkft2vSWK0phwGKkBq14 Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (FreeBSD) iD8DBQBAyIIo21dVnhLsBV0RAkq4AJ4jgEmBfQalBoIEiXA+0UwjH3fT2QCgl1xS KHR+IU8s7JgEtC3NK0zL3UE= =oZ/8 -----END PGP SIGNATURE----- --=-Qkft2vSWK0phwGKkBq14--