Message-ID: <5102DD61.1000207@gmail.com>
Date: Fri, 25 Jan 2013 17:30:41 -0200
From: alexandre - aldeia digital <adaldeia@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/17.0 Thunderbird/17.0
MIME-Version: 1.0
CC: pgsql-performance@postgresql.org
Subject: Re: Nested loop and simple join query - slow after upgrade
 to 9.2
References: <5102A5F7.2070103@gmail.com>
	<CAMkU=1zJ14Vki5zOXtaw7Q_8s4w=nUarfgx4n8SuRAxVOiayyQ@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1zJ14Vki5zOXtaw7Q_8s4w=nUarfgx4n8SuRAxVOiayyQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Em 25-01-2013 16:29, Jeff Janes escreveu:
> On Fri, Jan 25, 2013 at 7:34 AM, alexandre - aldeia digital
> <adaldeia@gmail.com> wrote:
>> Hi,
>>
>> Last weekend, we upgrade a PG from 8.4 to 9.2 version (full
>> pg_dump/restore/vacuum/analyze). After this, some simple join querys
>> became very slow, maybe because the use of nested loops. Bellow, an example
>> with nestedloop on and off:
>
> What happens if you bump up default_statistics_target by a factor of
> 10 or 100 and redo the analyze?

Before send the e-mail, the default_statistics_target was 500 and I 
return to 100 (default). I will try to set 1000.

> Here it is finding 39 times more rows than expected:
>
> Index Scan using ad_pc13t3_modpadrao on pc13t3 t1 (cost=0.00..6.21
> rows=1 width=65) (actual time=0.090..0.252 rows=39 loops=1)
>
> It would interesting to know why that is.

This is a partial index:

"ad_pc13t3_modpadrao" btree (pc13emp08p, pc13anoped, pc13codped, 
pc13codigo, pc13cor, pc13empins, pc13emp08, pc13tipin2, pc13insest) 
WHERE pc13emp08p = 0 AND pc13anoped = 0 AND pc13codped = 0 AND pc13item = 0

I can't connect to databse now. I will retry tests in sunday.

Best regards,

Alexandre


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