Re: help speeding up a query in postgres 8.4.5

On Tue, May 10, 2011 at 2:20 PM, Maria L. Wilson
<Maria(dot)L(dot)Wilson-1(at)nasa(dot)gov> wrote:
> 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?

Yeah, I'd try setting effective_cache_size=24GB, seq_page_cost=0.1,
random_page_cost=0.1 and see if you get a better plan. If possible,
can you post the EXPLAIN ANALYZE output with those settings for us?

If that doesn't cause the planner to use the indexes, then I'd be
suspicious that there is something wrong with those indexes that makes
the planner think it *can't* use them. It would be helpful to see the
EXPLAIN output after SET enable_seqscan=off.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company