From: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: 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
Date: Tue, 06 Nov 2012 17:31:15 -0200
Message-ID: <50996583.2080703@gmail.com> (raw)
In-Reply-To: <17866.1352229874@sss.pgh.pa.us>
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>
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.
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: rr.rosas@gmail.com, tgl@sss.pgh.pa.us, mmoncure@gmail.com
Subject: Re: Query completed in < 1s in PG 9.1 and ~ 700s in PG 9.2
In-Reply-To: <50996583.2080703@gmail.com>
* 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