Message-ID: <5099575A.1000503@gmail.com>
Date: Tue, 06 Nov 2012 16:30:50 -0200
From: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:10.0.10) Gecko/20121027 Icedove/10.0.10
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>, Merlin Moncure <mmoncure@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Query completed in < 1s in PG 9.1 and ~ 700s in PG
 9.2
References: <509944DE.6020400@gmail.com>
	<CAHyXU0ztB0e8eTidBbNXnhO6QjzaurXRGEBGbBt6P7oNY6vYcQ@mail.gmail.com>
	<15781.1352223387@sss.pgh.pa.us>
In-Reply-To: <15781.1352223387@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Em 06-11-2012 15:36, Tom Lane escreveu:
> Merlin Moncure<mmoncure@gmail.com>  writes:
>> Problem could also be no statistics (run ANALYZE to test) or some
>> other configuration problem (like index locale), or a bona fide
>> regression.
> I'm wondering about join_collapse_limit in particular --- if that wasn't
> cranked up in the 9.1 installation, it would be pure luck if you got a
> good query plan for an example like this.

I tried increasing it from 8 to 25 and it didn't make any difference.
>    Maybe that and/or other
> parameter settings didn't get transposed to the 9.2 installation.

diff /etc/postgresql/9.[12]/main/postgresql.conf

41c41
< data_directory = '/var/lib/postgresql/9.1/main'               # use 
data in another directory
---
 > data_directory = '/var/lib/postgresql/9.2/main'               # use 
data in another directory
43c43
< hba_file = '/etc/postgresql/9.1/main/pg_hba.conf'     # host-based 
authentication file
---
 > hba_file = '/etc/postgresql/9.2/main/pg_hba.conf'     # host-based 
authentication file
45c45
< ident_file = '/etc/postgresql/9.1/main/pg_ident.conf' # ident 
configuration file
---
 > ident_file = '/etc/postgresql/9.2/main/pg_ident.conf' # ident 
configuration file
49c49
< external_pid_file = '/var/run/postgresql/9.1-main.pid'                
# write an extra PID file
---
 > external_pid_file = '/var/run/postgresql/9.2-main.pid'                
# write an extra PID file
63c63
< port = 5433                           # (change requires restart)
---
 > port = 5432                           # (change requires restart)
556a557,558
 > ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
 > ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'

Any other idea?