From: Chris Cheston <ccheston@gmail.com>
To: Gavin M. Roy <gmr@ehpg.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: postgres 7.4 at 100%
Date: Wed, 30 Jun 2004 00:19:04 -0700
Message-ID: <e071108e040630001973bb802e@mail.gmail.com> (raw)
In-Reply-To: <40E192CC.90707@ehpg.net>
References: <e071108e04062423591d2b8aa9@mail.gmail.com>
<1088397641.1105.10.camel@localhost>
<17884.1088399982@sss.pgh.pa.us>
<200406280947.59420.josh@agliodbs.com>
<e071108e0406290100338c171d@mail.gmail.com>
<40E1266D.3040607@familyhealth.com.au>
<e071108e04062901377f6591c9@mail.gmail.com>
<40E192CC.90707@ehpg.net>
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@ehpg.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@familyhealth.com.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@postgresql.org)
> >
> >
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: ccheston@gmail.com, gmr@ehpg.net
Subject: Re: postgres 7.4 at 100%
In-Reply-To: <e071108e040630001973bb802e@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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