Message-ID: <50996583.2080703@gmail.com>
Date: Tue, 06 Nov 2012 17:31:15 -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>
CC: Merlin Moncure <mmoncure@gmail.com>,
 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>
	<5099525B.5070909@gmail.com>
	<CAHyXU0yKyz43s1GXO2QBDRwK8FeNqNAb6S0XUU66dSg+BD=xhg@mail.gmail.com>
	<50995D7C.9020108@gmail.com> <17866.1352229874@sss.pgh.pa.us>
In-Reply-To: <17866.1352229874@sss.pgh.pa.us>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Em 06-11-2012 17:24, Tom Lane escreveu:
> Rodrigo Rosenfeld Rosas<rr.rosas@gmail.com>  writes:
>> Em 06-11-2012 16:42, Merlin Moncure escreveu:
>>> Hm -- looking at your 'slow' 9.2 query, it is reporting that the query
>>> took 3 seconds  (reported times are in milliseconds).  How are you
>>> timing the data?  What happens when you run explain analyze
>>> <your_query>   from psql (as in, how long does it take)?
>> The time I reported in the tables of my first message were the time
>> reported by pgAdmin3 (compiled from source).
>> But I get similar time when I run like this:
>> time psql -p 5432 -f slow.sql db_name>  slow-9.2-again.explain
>> real    1m56.353s
>> user    0m0.068s
>> sys     0m0.020s
>> slow-9.2-again.explain: http://explain.depesz.com/s/zF1
> But that again shows only five seconds runtime.  If you repeat the query
> several dozen times in a row, run the same way each time, do you get
> consistent timings?

Yes, the timings are consistent here.

> Can you put together a self-contained test case to duplicate these
> results?  I'm prepared to believe there's some sort of planner
> regression involved here, but we'll never find it without a test case.

I'd love to, but I'm afraid I won't have time to do this any time soon. 
Maybe on Sunday. I'll see if I can get a script to generate the database 
on Sunday and hope for it to replicate the issue.

Would you mind if I coded it using Ruby? (can you run Ruby code in your 
computer?) I mean, for filling with some sample data.

Right now I need to concentrate on getting a working solution for 9.1 
and downgrade the database and work in several other requested fixes. 
That is why I'm out of time for writing this test case right now... I'll 
try to find some time on Sunday and will post here if I can replicate.

Thank you so much!

Rodrigo.