From: Knels, Udo <U.Knels@treubuch-it.de>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher
Date: Mon, 26 Sep 2016 15:59:58 +0000
Message-ID: <d47753011ac64182aa3fada94c6a4710@EXCH-1.ddd-do.de> (raw)
In-Reply-To: <283046e4-a37f-d8cb-85dc-52d1286e8ffb@BlueTreble.com>
References: <1d6ce510f1cf4dfcad73b15ff8ff98a4@EXCH-1.ddd-do.de>
<283046e4-a37f-d8cb-85dc-52d1286e8ffb@BlueTreble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi,
Thank you very much for your answers.
Yes, 50 rows aren't enough, but the original table has about 14 million rows and after analyzing the table I got the same result.
We changed our functions and used string_to_array instead of unnest and its ok.
It was not only a problem with one value to be selected. The problem exists with three or more too. Maybe the implementation of unnest has changed from V9.1 to V9.5. In V9.1 there was only one array as argument. Since V9.4 we can use more than one array as argument. And so the planner works different. So, if we change from one version to another in the future, we have to check the PostgreSQL-functions if the behaviour of the function or the planner has changed and then replace the function. It would be great if we could see this in the documentation.
Greetings
Udo Knels
treubuch IT GmbH
--
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: U.Knels@treubuch-it.de
Subject: Re: Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher
In-Reply-To: <d47753011ac64182aa3fada94c6a4710@EXCH-1.ddd-do.de>
* 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