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 67BD6D1B252 for ; Wed, 30 Jun 2004 04:19:07 -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 85089-02 for ; Wed, 30 Jun 2004 07:19:06 +0000 (GMT) Received: from mproxy.gmail.com (mproxy.gmail.com [216.239.56.246]) by svr1.postgresql.org (Postfix) with SMTP id 1B619D1B1CE for ; Wed, 30 Jun 2004 04:19:03 -0300 (ADT) Received: by mproxy.gmail.com with SMTP id w29so1631718cwb for ; Wed, 30 Jun 2004 00:19:04 -0700 (PDT) Received: by 10.38.164.38 with SMTP id m38mr31243rne; Wed, 30 Jun 2004 00:19:04 -0700 (PDT) Message-ID: Date: Wed, 30 Jun 2004 00:19:04 -0700 From: Chris Cheston To: "Gavin M. Roy" Subject: Re: postgres 7.4 at 100% Cc: pgsql-performance@postgresql.org In-Reply-To: <40E192CC.90707@ehpg.net> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <1088397641.1105.10.camel@localhost> <17884.1088399982@sss.pgh.pa.us> <200406280947.59420.josh@agliodbs.com> <40E1266D.3040607@familyhealth.com.au> <40E192CC.90707@ehpg.net> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/318 X-Sequence-Number: 7376 Oh my, creating an index has absolutely reduced the times it takes to query from around 700 ms to less than 1 ms! Thanks so much for all your help. You've saved me! One question: Why would I or would I not create multiple indexes in a table? I created another index in the same table an it's improved performance even more. Thanks, Chris On Tue, 29 Jun 2004 09:03:24 -0700, Gavin M. Roy wrote: > > Is the from field nullable? If not, try "create index calllogs_from on > calllogs ( from );" and then do an explain analyze of your query. > > Gavin > > > > Chris Cheston wrote: > > >ok i just vacuumed it and it's taking slightly longer now to execute > >(only about 8 ms longer, to around 701 ms). > > > >Not using indexes for calllogs(from)... should I? The values for > >calllogs(from) are not unique (sorry if I'm misunderstanding your > >point). > > > >Thanks, > > > >Chris > > > >On Tue, 29 Jun 2004 16:21:01 +0800, Christopher Kings-Lynne > > wrote: > > > > > >>>live=# explain analyze SELECT id FROM calllogs WHERE from = 'you'; > >>> QUERY PLAN > >>>---------------------------------------------------------------------------------------------------------- > >>> Seq Scan on calllogs (cost=0.00..136.11 rows=24 width=4) (actual > >>>time=0.30..574.72 rows=143485 loops=1) > >>> Filter: (from = 'you'::character varying) > >>> Total runtime: 676.24 msec > >>>(3 rows) > >>> > >>> > >>Have you got an index on calllogs(from)? > >> > >>Have you vacuumed and analyzed that table recently? > >> > >>Chris > >> > >> > >> > >> > > > >---------------------------(end of broadcast)--------------------------- > >TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > > > > >