From: Andy Colson <andy@squeakycode.net>
To: bricklen <bricklen@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Huge overestimation in rows expected results in bad plan
Date: Tue, 09 Nov 2010 16:48:04 -0600
Message-ID: <4CD9CFA4.7030600@squeakycode.net> (raw)
In-Reply-To: <AANLkTim=JS-tu5G1CvrPnvfSTj7=YSxu=kwNUP+hLVfG@mail.gmail.com>
References: <AANLkTim=JS-tu5G1CvrPnvfSTj7=YSxu=kwNUP+hLVfG@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andy@squeakycode.net, bricklen@gmail.com
Subject: Re: Huge overestimation in rows expected results in bad plan
In-Reply-To: <4CD9CFA4.7030600@squeakycode.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox