Skip site navigation (1) Skip section navigation (2)

Re: Subquery flattening causing sequential scan

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Jim Crate <jimcfl(at)gmail(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Subquery flattening causing sequential scan
Date: 2012-01-11 00:19:00
Message-ID: CA+TgmoZ4ALwpPdGTpmya3xydYep5JKe38nbVGavyrg8ai5Sa5w@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, Dec 27, 2011 at 12:29 PM, Jim Crate <jimcfl(at)gmail(dot)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 table is around 1M rows?  Are there some guidelines to when the planner will prefer not to use an available index?  I just had a look through postgresql.conf and noticed that I forgot to set effective_cache_size to something reasonable for a machine with 16GB of memory.  Would the default setting of 128MB cause this behavior?  I can't bounce the production server 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

In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2012-01-11 01:53:32
Subject: Re: partitioned table: differents plans, slow on some situations
Previous:From: Jesper KroghDate: 2012-01-10 21:42:35
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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group