Re: performance regression in 9.2/9.3

On 05/06/14 16:40, Merlin Moncure wrote:
> On Thu, Jun 5, 2014 at 6:32 AM, Linos <info(at)linos(dot)es> wrote:
>> Hello all,
>>
>> This is a continuation of the thread found here:
>> http://www.postgresql.org/message-id/538F2578.9080001@linos.es
>>
>> Considering this seems to be a problem with the planner I thought that maybe would be a better idea to post this problem here.
>>
>> To summarize the original thread I upgraded a medium (17Gb) database from PostgreSQL 8.4 to 9.3 and many of the queries my application uses started performing a lot slower, Merlin advised me to try disabling nestloop, this helped out for the particular query I was asking about but it is not a solution that I "can/would like" to use in the general case.
>>
>> I simplified a little bit the original query and I have added another one with same problem.
> I believe the basic problem (this is just one example; I've
> anecdotally seen this myself) is that changes in the query planner
> (which I don't follow and fully understand) in recent versions seem to
> be such that the planner makes better decisions in the presence of
> good information but in certain cases makes worse choices when dealing
> with bad information. Statistics errors tend to accumulate and
> magnify in complicated plans, especially when the SQL is not optimally
> written.
>
> I have no clue what the right solution is. There's been several
> discussions about 'plan risk' and trying to get the server to pick
> plans with better worse case behavior in cases where statistics are
> demonstrably suspicious. Maybe that would work but ISTM is a huge
> research item that won't get solved quickly or even necessarily pan
> out in the end. Nevertheless, user supplied test cases demonstrating
> performance regressions (bonus if it can be scripted out of
> generate_series) are going to be key drivers in finding a solution.
>
> merlin

I tried setting statistics to 10000 on albaran_entrada_cabecera.time_stamp_recepcion (query 1) and ticket_cabecera.fecha (query 2), query 2 is fixed after analyze with the new statistics target (with 5000 as target is fixed too) but query 1 doesn't improve.

Regards,
Miguel Angel.