Message-ID: <4CD9CFA4.7030600@squeakycode.net>
Date: Tue, 09 Nov 2010 16:48:04 -0600
From: Andy Colson <andy@squeakycode.net>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US;
	rv:1.9.2.12) Gecko/20101027 Lightning/1.0b2 Thunderbird/3.1.6
MIME-Version: 1.0
To: bricklen <bricklen@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Huge overestimation in rows expected results in bad
 plan
References: <AANLkTim=JS-tu5G1CvrPnvfSTj7=YSxu=kwNUP+hLVfG@mail.gmail.com>
In-Reply-To: <AANLkTim=JS-tu5G1CvrPnvfSTj7=YSxu=kwNUP+hLVfG@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 11/9/2010 3:26 PM, bricklen wrote:
> Hi,
>
> I have a query that is getting a pretty bad plan due to a massively
> incorrect count of expected rows. All tables in the query were vacuum
> analyzed right before the query was tested. Disabling nested loops
> gives a significantly faster result (4s vs 292s).
> Any thoughts on what I can change to make the planner generate a better plan?
>
>
> explain analyze
> select c.id, c.transactionid, c.clickgenerated, c.confirmed,
> c.rejected, cr.rejectedreason
> from conversion c
> inner join conversionrejected cr on cr.idconversion = c.id
> where date = '2010-11-06'
> and idaction = 12906
> and idaffiliate = 198338
> order by transactionid;
>
>

>           ->   Seq Scan on conversionrejected cr  (cost=0.00..191921.82
> rows=11012682 width=31) (actual time=0.003..1515.816 rows=11012682
> loops=72)
>   Total runtime: 292668.992 ms
>
>
>
>

Looks like the table stats are ok.  But its doing a sequential scan. 
Are you missing an index?

Also:

http://explain.depesz.com/

is magic.

-Andy