From: Tom Lane <tgl@sss.pgh.pa.us>
To: Igor Neyman <ineyman@perceptron.com>
cc: "Knels, Udo" <U.Knels@treubuch-it.de>,
        "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
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>
Comments: In-reply-to Igor Neyman <ineyman@perceptron.com>
	message dated "Thu, 22 Sep 2016 13:38:52 -0000"
Date: Thu, 22 Sep 2016 10:35:30 -0400
Message-ID: <15768.1474554930@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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