pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedquery optimization
4+ messages / 4 participants
[nested] [flat]
* query optimization
@ 2004-03-04 17:56 Charles Hauser <chauser@duke.edu>
2004-03-04 21:15 ` Re: query optimization Tom Lane <tgl@sss.pgh.pa.us>
2004-03-05 16:28 ` Re: query optimization Jonathan M. Gardner <jgardner@jonathangardner.net>
2004-06-10 15:45 ` Re: query optimization Franco Bruno Borghesi <franco@akyasociados.com.ar>
0 siblings, 3 replies; 4+ messages in thread
From: Charles Hauser @ 2004-03-04 17:56 UTC (permalink / raw)
To: pgsql-sql
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
(
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: query optimization
2004-03-04 17:56 query optimization Charles Hauser <chauser@duke.edu>
@ 2004-03-04 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2004-03-04 21:15 UTC (permalink / raw)
To: Charles Hauser <chauser@duke.edu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: query optimization
2004-03-04 17:56 query optimization Charles Hauser <chauser@duke.edu>
@ 2004-03-05 16:28 ` Jonathan M. Gardner <jgardner@jonathangardner.net>
2 siblings, 0 replies; 4+ messages in thread
From: Jonathan M. Gardner @ 2004-03-05 16:28 UTC (permalink / raw)
To: Charles Hauser <chauser@duke.edu>; +Cc: pgsql-sql
-----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-----
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: query optimization
2004-03-04 17:56 query optimization Charles Hauser <chauser@duke.edu>
@ 2004-06-10 15:45 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
2 siblings, 0 replies; 4+ messages in thread
From: Franco Bruno Borghesi @ 2004-06-10 15:45 UTC (permalink / raw)
To: Charles Hauser <chauser@duke.edu>; +Cc: pgsql-sql
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@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-06-10 15:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-04 17:56 query optimization Charles Hauser <chauser@duke.edu>
2004-03-04 21:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-05 16:28 ` Jonathan M. Gardner <jgardner@jonathangardner.net>
2004-06-10 15:45 ` Franco Bruno Borghesi <franco@akyasociados.com.ar>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox