From: Linos <info@linos.es>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Possible performance regression in PostgreSQL 9.2/9.3?
Date: Wed, 04 Jun 2014 21:58:29 +0200
Message-ID: <538F7A65.9050207@linos.es> (raw)
In-Reply-To: <CAHyXU0xOf6C5cnKozW+Ki6jUmjEcX6pcrqyDX8gYjKzPjBn=1g@mail.gmail.com>
References: <538F2578.9080001@linos.es>
<CAHyXU0xOf6C5cnKozW+Ki6jUmjEcX6pcrqyDX8gYjKzPjBn=1g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 04/06/14 21:36, Merlin Moncure wrote:
> On Wed, Jun 4, 2014 at 8:56 AM, Linos <info@linos.es> wrote:
>> Hello,
>>
>> Some days ago I upgraded from 8.4 to 9.3, after the upgrade some queries started performing a lot slower, the query I am using in this example is pasted here:
>>
>> http://pastebin.com/71DjEC21
>>
>>
>> Considering it is a production database users are complaining because queries are much slower than before, so I tried to downgrade to 9.2 with the same result as 9.3, I finally restored the database on 8.4 and the query is as fast as before.
>>
>> All this tests are done on Debian Squeeze with 2.6.32-5-amd64 kernel version, the hardware is Intel Xeon E5520, 32Gb ECC RAM, the storage is software RAID 10 with 4 SEAGATE ST3146356SS SAS drives.
>>
>> postgresql.conf:
>> max_connections = 250
>> shared_buffers = 6144MB
>> temp_buffers = 8MB
>> max_prepared_transactions = 0
>> work_mem = 24MB
>> maintenance_work_mem = 384MB
>> max_stack_depth = 7MB
>> default_statistics_target = 150
>> effective_cache_size = 24576MB
>>
>>
>> 9.3 explain:
>> http://explain.depesz.com/s/jP7o
>>
>> 9.3 explain analyze:
>> http://explain.depesz.com/s/6UQT
>>
>> 9.2 explain:
>> http://explain.depesz.com/s/EW1g
>>
>> 8.4 explain:
>> http://explain.depesz.com/s/iAba
>>
>> 8.4 explain analyze:
>> http://explain.depesz.com/s/MPt
>>
>> It seems to me that the total estimated cost went too high in 9.2 and 9.3 but I am not sure why, I tried commenting out part of the query and disabling indexonlyscan but still I have very bad timings and estimates.
>>
>> The dump file is the same for all versions and after the restore process ended I did vacuum analyze on the restored database in all versions.
>> http://www.postgresql.org/mailpref/pgsql-performance
> The rowcount estimates are garbage on all versions so a good execution
> plan can be chalked up to chance. That being said, it seems like
> we're getting an awful lot of regressions of this type with recent
> versions.
>
> Can you try re-running this query with enable_nestloop and/or
> enable_material disabled? (you can disable them for a particular
> session via: set enable_material = false;) . This is a "ghetto fix"
> but worth trying. If it was me, I'd be simplifying and optimizing the
> query.
>
> merlin
>
>
Much better with this options set to false, thank you Merlin, even better than 8.4
9.3 explain analyze with enable_nestloop and enable_material set to false.
http://explain.depesz.com/s/94D
The thing is I have plenty of queries that are now a lot slower than before, this is only one example. I would like to find a fix or workaround.
I can downgrade to 9.1, I didn't try on 9.1 but it's the first version that supports exceptions inside plpython and I would like to use them. Do you think this situation would be better on 9.1?
Or maybe can I disable material and nestloop on postgresql.conf? I thought was bad to trick the planner but given this strange behavior I am not sure anymore.
Regards,
Miguel Angel.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: info@linos.es, mmoncure@gmail.com
Subject: Re: Possible performance regression in PostgreSQL 9.2/9.3?
In-Reply-To: <538F7A65.9050207@linos.es>
* 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