From: Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS AND APPLICATIONS, INC] <m.l.wilson@nasa.gov>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: help speeding up a query in postgres 8.4.5
Date: Tue, 10 May 2011 14:20:09 -0400
Message-ID: <4DC981D9.3070008@nasa.gov> (raw)
In-Reply-To: <BANLkTi=d_ohr61DianQ7Q-23+z5fCisD9g@mail.gmail.com>
References: <4D9B6CBA.2050609@nasa.gov>
<BANLkTim+36AxCNq+KPCFwaE9fGKTn-8S5Q@mail.gmail.com>
<4DC97A40.7010905@nasa.gov>
<BANLkTi=d_ohr61DianQ7Q-23+z5fCisD9g@mail.gmail.com>
haven't tested a composite index
invsensor is 2,003,980 rows and 219MB
granver is 5,138,730 rows and 556MB
the machine has 32G memory
seq_page_cost, random_page_costs & effective_cache_size are set to the
defaults (1,4, and 128MB) - looks like they could be bumped up.
Got any recommendations?
Maria
On 5/10/11 1:59 PM, Robert Haas wrote:
> [ woops, accidentally replied off-list, trying again ]
>
> On Tue, May 10, 2011 at 1:47 PM, Maria L. Wilson
> <Maria.L.Wilson-1@nasa.gov> wrote:
>> thanks for taking a look at this.... and it's never too late!!
>>
>> I've tried bumping up work_mem and did not see any improvements -
>> All the indexes do exist that you asked.... see below....
>> Any other ideas?
>>
>> CREATE INDEX invsnsr_idx1
>> ON invsensor
>> USING btree
>> (granule_id);
>>
>> CREATE INDEX invsnsr_idx2
>> ON invsensor
>> USING btree
>> (sensor_id);
> What about a composite index on both columns?
>
>> CREATE UNIQUE INDEX granver_idx1
>> ON gran_ver
>> USING btree
>> (granule_id);
> It's a bit surprising to me that this isn't getting used. How big are
> these tables, and how much memory do you have, and what values are you
> using for seq_page_cost/random_page_cost/effective_cache_size?
>
> ...Robert
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: Maria.L.Wilson-1@nasa.gov, robertmhaas@gmail.com, m.l.wilson@nasa.gov
Subject: Re: help speeding up a query in postgres 8.4.5
In-Reply-To: <4DC981D9.3070008@nasa.gov>
* 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