Postgres Pro
Downloads
Home   >   mailing lists

Re: Subquery flattening causing sequential scan - Mailing list pgsql-performance

From Robert Haas
Subject Re: Subquery flattening causing sequential scan
Date
Msg-id CA+TgmoZ4ALwpPdGTpmya3xydYep5JKe38nbVGavyrg8ai5Sa5w@mail.gmail.com
Whole thread Raw
In response to Subquery flattening causing sequential scan  (Jim Crate <jimcfl@gmail.com>)
List pgsql-performance
Tree view 
On Tue, Dec 27, 2011 at 12:29 PM, Jim Crate <jimcfl@gmail.com> wrote:
> My question is why does it do a seq scan when it flattens this subquery into a JOIN?  Is it because the emsg_messages
tableis around 1M rows?  Are there some guidelines to when the planner will prefer not to use an available index?  I
justhad a look through postgresql.conf and noticed that I forgot to set effective_cache_size to something reasonable
fora machine with 16GB of memory.  Would the default setting of 128MB cause this behavior?  I can't bounce the
productionserver midday to test that change. 

You wouldn't need to bounce the production server to test that.  You
could just use SET in the session you were testing from.

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

pgsql-performance by date:

Previous
From: Jesper Krogh
Date:
Subject: Re: Query planner doesn't use index scan on tsvector GIN index if LIMIT is specifiedQuery planner doesn't use index scan on tsvector GIN index if LIMIT is specified
Next
From: Robert Haas
Date:
Subject: Re: partitioned table: differents plans, slow on some situations