Message-ID: <49C99732.8080703@agliodbs.com>
Date: Tue, 24 Mar 2009 19:30:10 -0700
From: Josh Berkus <josh@agliodbs.com>
Organization: Aglio Database Solutions
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.5; en-US;
	rv:1.9.1b3pre) Gecko/20090223 Thunderbird/3.0b2
MIME-Version: 1.0
To: Bryan Murphy <bmurphy1976@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Help Me Understand Why I'm Getting a Bad Query Plan
References: <7fd310d10903231203r1bd37b8cn8baeadae83253f44@mail.gmail.com>
	<7fd310d10903241521t645462bdpb3b8aee40b4f3ae4@mail.gmail.com>
In-Reply-To: <7fd310d10903241521t645462bdpb3b8aee40b4f3ae4@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Brian,

> I hate to nag, but could anybody help me with this?  We have a few
> related queries that are causing noticeable service delays in our
> production system.  I've tried a number of different things, but I'm
> running out of ideas and don't know what to do next.

For some reason, your first post didn't make it to the list, which is 
why nobody responded.


>> I've got a query on our production system that isn't choosing a good
>> plan.  I can't see why it's choosing to do a sequential scan on the
>> ItemExperienceLog table.  That table is about 800mb and has about 2.5
>> million records.  This example query only returns 4 records.  I've
>> tried upping the statics for ItemExperienceLog.VistorId and
>> ItemExperienceLog.ItemId to 1000 (from out default of 100) with no
>> success.

Yes, that is kind of inexplicable.  For some reason, it's assigning a 
very high cost to the nestloops, which is why it wants to avoid them 
with a seq scan.  Can you try lowering cpu_index_cost to 0.001 and see 
how that affects the plan?

--Josh