Date: Wed, 17 Jul 2013 14:40:00 -0700
From: David Kerr <dmk@mr-paradox.net>
To: Ellen Rothman <erothman@datalinedata.com>
Cc: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Seq Scan vs Index on Identical Tables in Two Different Databases
Message-ID: <20130717214000.GA84074@mr-paradox.net>
References: 
 <d2c495ac125a4e94acd463a6981136f2@BLUPR08MB166.namprd08.prod.outlook.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <d2c495ac125a4e94acd463a6981136f2@BLUPR08MB166.namprd08.prod.outlook.com>
User-Agent: Mutt/1.4.2.3i
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Wed, Jul 17, 2013 at 07:50:06PM +0000, Ellen Rothman wrote:
- I have the same table definition in two different databases on the same computer. When I explain a simple query in both of them, one database uses a sequence scan and the other uses an index scan.  If I try to run the Seq Scan version without the where clause restricting the value of uniqueid, it uses all of the memory on my computer and never completes.
- 
- How can I get the Seq Scan version to use an index scan?
- 
- Explain results - good version:
- "GroupAggregate  (cost=0.00..173.78 rows=1 width=15)"
- "  ->  Index Scan using pubcoop_ext_idx1 on pubcoop_ext  (cost=0.00..173.77 rows=1 width=15)"
- "        Index Cond: (uniqueid < '000000009'::bpchar)"
- 
- Explain results - problem version:
- "HashAggregate  (cost=13540397.84..13540398.51 rows=67 width=18)"
- "  ->  Seq Scan on pubcoop_ext  (cost=0.00..13360259.50 rows=36027667 width=18)"
- "        Filter: (uniqueid < '000000009'::bpchar)"

(Assuming that your postgresql.conf is the same across both systems and that
you've run vanilla analyze against each table... )

I ran into a similar problem before and it revolved around the somewhat random nature of
a vaccum analyze. To solve the problem i increased the statistics_target for the table
on the box that was performing poorly and ran analyze.

I believe that worked because basically the default_statistics_taget of 100 wasn't
catching enough info about that record range to make an index appealing to the optimizer
on the new box where the old box it was.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance