Re: performance regression in 9.2/9.3

On Mon, Jun 9, 2014 at 10:00 AM, Linos <info(at)linos(dot)es> wrote:
> On 09/06/14 16:55, Merlin Moncure wrote:
>> On Mon, Jun 9, 2014 at 9:51 AM, Linos <info(at)linos(dot)es> wrote:
>>> Hello,
>>>
>>> Is this information enough? I could try to assemble a complete test case but I have very little time right now because I am trying to meet a very difficult deadline.
>>>
>>> I will do ASAP if needed.
>> It is not -- it was enough to diagnose a potential problem but not the
>> solution. Tom was pretty clear: "I'd like to see a self-contained
>> test case, by which I mean full details about the table/view schemas;
>> it's not clear whether the actual data is very important here.".
>>
>> merlin
>
> Merlin, in the email I replied to are attached the table/view schemas, I was referring to this information as enough or not. Tom said "full details about the table/view schemas" and these details are attached to the original email I replied to.

A self contained test case would generally imply a precise sequence of
steps (possibly with supplied data, or some manipulations via
generate_series) that would reproduce the issue locally. Since data
may not be required, you might be able to get away with a 'schema only
dump', but you'd need to make sure to include necessary statistics
(mostly what you'd need is in pg_statistic which you'd have to join
against pg_class, pg_attribute and pg_namespace).

Ideally, you'd be able to restore your schema only dump on a blank
database with autovacuum disabled, hack in your statistics, and verify
your query produced the same plan. Then (and only then) you could tar
up your schema only file, the statistics data, and the query to update
the data, and your query with the bad plan which you've triple checked
matched your problem condition's plan, and send it to Tom. There
might be some things I've missed but getting a blank database to
reproduce your problem with a minimum number of steps is key.

merlin