From: Tom Lane <tgl@sss.pgh.pa.us>
To: Igor Neyman <ineyman@perceptron.com>
Cc: Knels, Udo <U.Knels@treubuch-it.de>
Cc: 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: Thu, 22 Sep 2016 10:35:30 -0400
Message-ID: <15768.1474554930@sss.pgh.pa.us> (raw)
In-Reply-To: <CY4PR07MB287241DA8EF831A987F7DFEFDAC90@CY4PR07MB2872.namprd07.prod.outlook.com>
References: <1d6ce510f1cf4dfcad73b15ff8ff98a4@EXCH-1.ddd-do.de>
<283046e4-a37f-d8cb-85dc-52d1286e8ffb@BlueTreble.com>
<de4134e10aa14dbeb7e0850ccfc4ae73@EXCH-1.ddd-do.de>
<CY4PR07MB287241DA8EF831A987F7DFEFDAC90@CY4PR07MB2872.namprd07.prod.outlook.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Igor Neyman <ineyman@perceptron.com> writes:
> table_a is too small, just 50 records.
> Optimizer decided (correctly) that Seq Scan is cheaper than using an index.
Yeah. The given test case is quite useless for demonstrating that you
have a problem, since it's actually *faster* on 9.5 than 9.1.
What I suspect is happening is that 9.2 and up assume that an unnest()
will produce 100 rows, whereas 9.1 assumed it would produce only 1 row.
The latter happened to be more accurate for this specific case, though
in general it could result in selection of very bad plans.
If you are intending only one value be selected, don't use unnest();
you'd be better off with "(string_to_array('5010010000',','))[1]"
or something like that.
In the long run we should teach the planner how to produce better
estimates for unnest-on-a-constant-array, though I'm unsure whether
that would help your real application as opposed to this test case.
regards, tom lane
--
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: tgl@sss.pgh.pa.us, ineyman@perceptron.com, 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: <15768.1474554930@sss.pgh.pa.us>
* 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