From: Rodrigo Rosenfeld Rosas <rr.rosas@gmail.com>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query completed in < 1s in PG 9.1 and ~ 700s in PG 9.2
Date: Wed, 07 Nov 2012 09:16:09 -0200
Message-ID: <509A42F9.6040403@gmail.com> (raw)
In-Reply-To: <CAHyXU0xRk0LCj6NYuTAUaWN2FDAWj+jHgH2niDAGtb1w4QT83w@mail.gmail.com>
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>
<50996583.2080703@gmail.com>
<18371.1352231105@sss.pgh.pa.us>
<CAHyXU0xRk0LCj6NYuTAUaWN2FDAWj+jHgH2niDAGtb1w4QT83w@mail.gmail.com>
Em 06-11-2012 19:11, Merlin Moncure escreveu:
> On Tue, Nov 6, 2012 at 1:45 PM, Tom Lane<tgl@sss.pgh.pa.us> wrote:
>> Rodrigo Rosenfeld Rosas<rr.rosas@gmail.com> writes:
>>> Em 06-11-2012 17:24, Tom Lane escreveu:
>>>> 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.
>> No objection.
> hm, wouldn't timing the time to generate a raw EXPLAIN (that is,
> without ANALYZE) give a rough estimate of planning time? better to
> rule it out before OP goes to the trouble...
This was a great guess! Congrats, Merlin:
PG 9.1 (port 5433):
time psql -p 5433 -f slow-explain-only.sql db_name > /dev/null
real 0m0.284s
user 0m0.068s
sys 0m0.012s
time psql -p 5432 -f slow-explain-only.sql db_name > /dev/null
real 2m10.409s
user 0m0.056s
sys 0m0.016s
time psql -p 5433 -f fast-explain-only.sql db_name > /dev/null
real 0m0.264s
user 0m0.064s
sys 0m0.020s
time psql -p 5432 -f fast-explain-only.sql db_name > /dev/null
real 12m25.084s
user 0m0.052s
sys 0m0.020s
This is great news because it makes it easier for me to provide a
test-case since the results were the same in my test database (which is
mostly empty):
time psql -p 5432 -f fast-explain-only.sql db_test > /dev/null
real 6m0.414s
user 0m0.064s
sys 0m0.024s
I'm in Brazil which is 3 hours behind NY, where my client is. Later when
they start their journey I'll ask them if I can send our plain database
schema to make it even easier. Otherwise, if they prefer me to create
another database schema or to drop the unrelated tables first I'll do
that. Maybe they could be afraid of SQL injection attacks although I
believe we're currently free of errors of this nature in our applications.
Thank you so much for narrowing down the real problem with 9.2.
After this regression is fixed in 9.2 I'd like to know if it would be
possible to optimize the planner so that slow.sql could perform as well
as fast.sql. I believe the unique index on (transaction_id, type_id)
helps slow.sql to perform better but if the planner could be smart
enough to understand that slow.sql and fast.sql are equivalents I'd
prefer to use slow.sql instead of fast.sql as it reads better and it is
easier to maintain and write tests for and reduces our database log files.
Cheers,
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, mmoncure@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: Query completed in < 1s in PG 9.1 and ~ 700s in PG 9.2
In-Reply-To: <509A42F9.6040403@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