Re: postgres 7.4 at 100%

From: Duane Lee - EGOVX <DLee(at)mail(dot)maricopa(dot)gov>
To: "'Chris Cheston'" <ccheston(at)gmail(dot)com>, "Gavin M(dot) Roy" <gmr(at)ehpg(dot)net>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: postgres 7.4 at 100%
Date: 2004-06-30 16:32:42
Message-ID: 64EDC403A1417B4299488BAE87CA7CBF01CD0EA9@maricopa_xcng0
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Creating indexes on a table affects insert performance depending on the
number of indexes that have to be populated. From a query standpoint,
indexes are a godsend in most cases.

Duane

-----Original Message-----
From: Chris Cheston [mailto:ccheston(at)gmail(dot)com]
Sent: Wednesday, June 30, 2004 12:19 AM
To: Gavin M. Roy
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] postgres 7.4 at 100%

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 <gmr(at)ehpg(dot)net> 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
> ><chriskl(at)familyhealth(dot)com(dot)au> 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(at)postgresql(dot)org)
> >
> >
>
>

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2004-06-30 17:06:57 Re: reindex and copy - deadlock?
Previous Message Litao Wu 2004-06-30 16:17:46 Re: reindex and copy - deadlock?