Message-ID: <509AD4C4.5070003@gmail.com>
Date: Wed, 07 Nov 2012 19:38:12 -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>
	<50996583.2080703@gmail.com> <18371.1352231105@sss.pgh.pa.us>
	<CAHyXU0xRk0LCj6NYuTAUaWN2FDAWj+jHgH2niDAGtb1w4QT83w@mail.gmail.com>
	<509A42F9.6040403@gmail.com>
	<CAHyXU0wn4vftY2Q4yFZNLu9XNeRRjTUnMXKw-BmmvixJMMvohw@mail.gmail.com>
	<10569.1352307514@sss.pgh.pa.us>
In-Reply-To: <10569.1352307514@sss.pgh.pa.us>
Content-Type: multipart/mixed; boundary="------------050202010807030906030305"

This is a multi-part message in MIME format.
--------------050202010807030906030305
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Em 07-11-2012 14:58, Tom Lane escreveu:
> Merlin Moncure<mmoncure@gmail.com>  writes:
>> On Wed, Nov 7, 2012 at 5:16 AM, Rodrigo Rosenfeld Rosas
>> <rr.rosas@gmail.com>  wrote:
>>> This was a great guess! Congrats, Merlin:
>> Heh -- that was tom's guess, not mine.  What this does is confirm the
>> planner regression and that elevates the importance of Tom's request
>> to get sample data so we (he) can fix it.
> Well, the fact that it's a planner runtime problem and not a
> quality-of-plan problem is new information (I'd been assuming the
> latter).  Given that, it's possible it's already fixed:
> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ca2d6a6cef5740b29406980eb8d21d44da32634b
> but I'd still want to see a test case to be sure.  In any case,
> it's not clear what's the critical difference between the "fast" and
> "slow" versions of the query.
>
> 			regards, tom lane

Ok, I could finally strip part of my database schema that will allow you 
to run the explain query and reproduce the issue.

There is a simple SQL dump in plain format that you can restore both on 
9.1 and 9.2 and an example EXPLAIN query so that you can see the 
difference between both versions.

Please keep me up to date with regards to any progress. Let me know if 
the commit above fixed this issue.

Thanks in advance,

Rodrigo.


--------------050202010807030906030305
Content-Type: application/x-tbz;
 name="use_case.tar.bz2"
Content-Transfer-Encoding: base64
Content-Disposition: attachment;
 filename="use_case.tar.bz2"

QlpoOTFBWSZTWZglCzkAE8//jdgQAkBa5//6P+//4P////AACIAEAACACGAMflvcOfDldXnd
KrgQApNtlqUNACS2AyUG1NUUPUHlNNA9IaG1AAAANAAAAAgmp6aMlR6g0NAGgAA00BoAAAAD
mE0BoDRowjQYjTEyYmgwjQMgGTAZ6pRCJtRo2pNqNMT1GQNMgBk0AaAADQESSCNTTFTwo8nq
melPUaGJ5NRtQenqjTIPKAaaAESRCZGgmIEaTT1TJ5EAaPUGmgA0GjTE07T/vxOIsWVxvBdk
QdmGggd4hLqLIlERIQDCKFCFm3HtAWiQVJFoBaG8Rd/RRA9AMgICA8QuUljBCFay6zbqqllw
buSM2JouB012oK8pO5k8TR3mID6RZVZjJoyGfLuCHmVeHb2QAFWVakr9PQsluSy1bZezJOWN
MlC7HBUDxABEVyIihegpHkNm948/TusJuhe3yc1ZQeBYBmjvqLpMnP5lxW2hbqhj+0Q7pt5G
5/Kha0ZzOa82uNeX349+qTb369GdeMWl0UsXHvF62XCt/dcd3PLXCFwpVsUJ+VBxTqSdxFLS
eE5ZGFCSLSSCy8xKmaIsKw2k4cfEV0E2oDDNSQfIV+ehVUpUhNZiIkkkklLuXQBbC3W6+JcB
SycOMYQgeJTx6Lb+mfluF937RDQRNO6OcNH1azkHStbkMJ8d7zEl7EMJ8Te8wNrWFyGMtaYE
tYheWtLktYheWtLstYheego0PYdmGxYt7VJJAAAAd+9eW8OES3uW283KDZLd9u7AAAABd/sS
35ubl9p1O52OG4M8BR3O/XpgLuL69OuM3uAtaXIaTrM+gufsgEAABp51k6fPsm+z5fBOee/2
9rv8LwJu15m1+6x5PjXLLovF0LAOZZiBFbQS98x0lm/td1gByaS93MaTTMsxNU6PAk6Fl0mE
nxQo8Yi74DARcwgv+xRek6/lwUCWTQN2/xJXLBk0TTepMRFnxSCBSpOdKGVImCBQKWmvHmjK
8GB3KEFVKgQElUqVShCFisFEJgJMJGMwMAzyxAyPWNBhzJYsmdie4TRWaySVgei95AcU14pp
JZu5/67zI262tNNWQYyDGQZKJYNV2ro5cKDNfIsar+ujZJJnKkJJCqOVhCISxpTosC+YXLXI
tcVwLL8vPovHx7Leacl63W5mR1LdtummrIMZBjIMZGXhe9yWlGq2ve5LSirWsS0oktKJiGWR
v2nHvzMzM5PHpprmZGZmZEt4uuABllnnyAe0B/wwAKERpEYPcB98D4PPOHnVFVRVUVVFVTUF
JVPqwxwK8Jdz6cL/bF2kWQAPL4ufyLZepYvUjSxMQAx29i8K1r35LaqpqFR8/QLddhGvhBDB
V600gvEt3hXsFpToRO4WlbF+C4rwoBzoFL9AL3GYU/A0L59gLgtJL10xWIBcuUIL2LSN63oG
mwIfTvC9voXtPaLoyMkiTmF4VsoVRISEhISGdUhFpAJmLuQOGaYLsBdgdiGALjYFx1uAZGAI
WTavv89nJA+bF647xDoZAcgfn7DZvWu1bAhxEO0XmtBtS/nfMDgXXMT52CHdtNfnBecMu8eA
RQIImi7xY4p1XFaXeUtrmSxEusTohMQXBdisBDgLBDJMBdwuaDkFhcigMVkW4DFgC6C8yvE3
r0YWxihCMgtCzFBgoGzWlOjimiC6C/wN1tAE7Tmvc7hc88tqZQFxi0ByEN4eo9vNbC6ZAL3a
Q33wQuvBBrmtmhhgsaKWLiC8C60A5QvBdOQGBgrAFOwwF8EAzAc0Cy4KnXRcF2l0NRaQTJfC
li71zKhISEhISEzi9nghJA2ivEbnphCfANwpAl8kgQGgIQIQKovhd68RsCWlQWqKZFqUVCi4
xUth3lyMXKxcYuRi5dsS2X9iEP54DcIQhBxChoVLQq0alRrGSUXllAiWUM1UZFZhMguKSEAg
9Ukm6LYibhWZMFhbGZMSQhCEIQCQjb2p/Mkty8ktjokkn02SF/3+nlNSHQqh7Jk2+xd49ZSI
OoDwALG7jBdFfoEtnmqhwACx3IZdPqwXACtyH0nXkuLREwxiai/kAdMQDgUXUi2EJ7EoRIKP
usYIB9HZ+zJD4wF5+S2O3NCgfOFDGCkT8IkC0LBCD6RfJ8rySXVgN8KCLgwLQsEIRLQLQsEI
QSoUEJ5HBJFDOIgTkqhBd1CCUPCiiIhzKd2ZnX99bXL4CvYaJ7LCClg0xMsbHV7/f8Pgu3xE
PTZG23HK9yMIFQlcy14LeuzutaXYYU41sACgC8ASKjYwrC8ZumSzCwkkRKpSlKSpFkLIcwXH
1nfw59K3aCHr9RoLZd6B7wp7upqAt+m2+SSHYcVA5oGQvRAiEAXBMb42JrqgFFwK1uK/+7Pp
27V7he84kLULxpTwk8HwsCYL6YgTsA5htDDIJR3qdYAtc1F8RdUXesQOQyQPEYhIN1utKlIE
RTjDzMjrwCstHjsVYd9xXL7uMA44ZgnyZCBiAqFsDqk2J5EDmRAiSERAhAFMlXn16i/KdzIS
BxAmI2KB6J3QAxcNi4PMFsL6EAwii4GmhcIcCIg7AFgXiK1VGdTjgXmigYK41bZcqxLVhJZC
SySEsluoAWRUFoAAiBttmuzWWXZKuCgpxQOIC7ltdAMM8iwhZVDYJSBQAekMNdaDtNfrVdoL
ggGYBuJAhCEIQhCEIWLIW2XZGIwIigQIEIoQIJBiJBilhBT7JmCwuInhyU+Qhw9egviHegEA
DZ2+RQel4xFZAE+vYcwwF/A53Cx8KQKemYiUou9QNRudiW7yIixDw9SqFPiUIfEtlyS5QHvB
gCbNt+3qfWWQ27N2kLGqyme8qhbiXyMJ0OWCgcd6mSAeZtDYtjLMmxMggJVvQLO3wugZDvWE
Nya1NrpDeG+3LPSzgOmSZoE3C80NmIX3FtkrCBUiyRwyzFCS1ZJUpLSyIpQiBMssSZEW2xFk
syyzWJcB9sKCBqLWupQB5CG0E2pxXhgJkZ7G1CxA57lF5Ig+ZuibTq9NhXcn3vsO+STp/qhC
EcuYhCEIiIiIbV2D0EgnJqhb4lKWG9rKBdDdMbV+cBdxzVHxAD4UMgPyALAEy+37dT6oAf/F
3JFOFCQmCULOQA==
--------------050202010807030906030305--