X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id E059653A99 for ; Thu, 7 Apr 2005 16:27:06 +0100 (BST) 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 11535-02 for ; Thu, 7 Apr 2005 15:27:05 +0000 (GMT) Received: from mail.99main.com (mail.99main.com [208.28.184.11]) by svr1.postgresql.org (Postfix) with ESMTP id 74C6353A7E for ; Thu, 7 Apr 2005 16:27:04 +0100 (BST) Received: from 99main.com (localhost [127.0.0.1]) by mail.99main.com (8.12.8+Sun/8.12.8) with ESMTP id j37FR3jj012087; Thu, 7 Apr 2005 11:27:03 -0400 (EDT) From: "Keith Worthington" To: "Joel Fradkin" Cc: "PostgreSQL Perform" Reply-To: KeithW@narrowpathinc.com Subject: Re: Any way to speed this up? Date: Thu, 7 Apr 2005 11:27:03 -0400 Message-Id: <20050407152150.M93433@narrowpathinc.com> In-Reply-To: <005301c53b84$6b83f8a0$797ba8c0@jfradkin> References: <005301c53b84$6b83f8a0$797ba8c0@jfradkin> X-Mailer: Open WebMail 2.50 20050106 X-OriginatingIP: 68.235.22.242 (npkeithw) MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.008 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/157 X-Sequence-Number: 11554 On Thu, 07 Apr 2005 11:13:57 -0400, Joel Fradkin wrote [snip] > " -> Sort (cost=393.76..394.61 rows=338 width=48) (actual > time=62.000..62.000 rows=441 loops=1)" > > " Sort Key: l.locationid" > > " -> Index Scan using ix_location on tbllocation l > > (cost=0.00..379.56 rows=338 width=48) (actual time=15.000..62.000 rows=441 > loops=1)" > > " Index Cond: ('SAKS'::text = (clientnum)::text)" > > " -> Sort (cost=59478.03..59909.58 rows=172618 width=75) (actual > time=46844.000..46985.000 rows=159960 loops=1)" > > " Sort Key: a.locationid" [snip] > > CREATE TABLE tblassociate [snip] > > CONSTRAINT pk_tblassociate PRIMARY KEY (clientnum, associateid), > > CONSTRAINT ix_tblassociate UNIQUE (clientnum, associatenum) > [snip] > > Joel Fradkin Joel, I am REALLY new at this and struggling to understand EXPLAIN ANALYZE output but for what it is worth it looks like the sort on a.locationid is taking up a lot of the time. I do not see an index on that column. I would suggest indexing tblassociate.locationid and seeing if that helps. Kind Regards, Keith