Re: [PERFORM] Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher

[Knels, Udo]

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

Re: [PERFORM] Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher

[Tom Lane]

Igor Neyman  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('501001',','))[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

Re: [PERFORM] Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher

[Igor Neyman]

-Original Message-
From: pgsql-performance-ow...@postgresql.org 
[mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Knels, Udo
Sent: Thursday, September 22, 2016 8:40 AM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Problem with performance using query with unnest after 
migrating from V9.1 to V9.2 and higher

Hi,

I tried the following on the upgraded database:
analyze schema_test.table_a;

But the result is the same. 

https://explain.depesz.com/s/hsx5

"Sort  (cost=5.94..6.01 rows=26 width=6) (actual time=0.199..0.200 rows=3 
loops=1)"
"  Sort Key: table_a.col0002"
"  Sort Method: quicksort  Memory: 25kB"
"  Buffers: shared hit=1"
"  ->  HashAggregate  (cost=5.07..5.33 rows=26 width=6) (actual 
time=0.161..0.163 rows=3 loops=1)"
"Group Key: table_a.col0002"
"Buffers: shared hit=1"
"->  Hash Semi Join  (cost=2.76..4.95 rows=50 width=6) (actual 
time=0.070..0.133 rows=26 loops=1)"
"  Hash Cond: ((table_a.col0001)::text = 
(unnest('{501001}'::text[])))"
"  Buffers: shared hit=1"
"  ->  Seq Scan on table_a  (cost=0.00..1.50 rows=50 width=17) 
(actual time=0.015..0.034 rows=50 loops=1)"
"Buffers: shared hit=1"
"  ->  Hash  (cost=1.51..1.51 rows=100 width=32) (actual 
time=0.028..0.028 rows=1 loops=1)"
"Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"->  Result  (cost=0.00..0.51 rows=100 width=0) (actual 
time=0.015..0.017 rows=1 loops=1)"
"Planning time: 0.653 ms"
"Execution time: 0.326 ms"

Greetings

Udo Knels
treubuch IT GmbH
_

table_a is too small, just 50 records.
Optimizer decided (correctly) that Seq Scan is cheaper than using an index.

Regards,
Igor Neyman



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher

[Knels, Udo]

Hi,

I tried the following on the upgraded database:
analyze schema_test.table_a;

But the result is the same. 

https://explain.depesz.com/s/hsx5

"Sort  (cost=5.94..6.01 rows=26 width=6) (actual time=0.199..0.200 rows=3 
loops=1)"
"  Sort Key: table_a.col0002"
"  Sort Method: quicksort  Memory: 25kB"
"  Buffers: shared hit=1"
"  ->  HashAggregate  (cost=5.07..5.33 rows=26 width=6) (actual 
time=0.161..0.163 rows=3 loops=1)"
"Group Key: table_a.col0002"
"Buffers: shared hit=1"
"->  Hash Semi Join  (cost=2.76..4.95 rows=50 width=6) (actual 
time=0.070..0.133 rows=26 loops=1)"
"  Hash Cond: ((table_a.col0001)::text = 
(unnest('{501001}'::text[])))"
"  Buffers: shared hit=1"
"  ->  Seq Scan on table_a  (cost=0.00..1.50 rows=50 width=17) 
(actual time=0.015..0.034 rows=50 loops=1)"
"Buffers: shared hit=1"
"  ->  Hash  (cost=1.51..1.51 rows=100 width=32) (actual 
time=0.028..0.028 rows=1 loops=1)"
"Buckets: 1024  Batches: 1  Memory Usage: 9kB"
"->  Result  (cost=0.00..0.51 rows=100 width=0) (actual 
time=0.015..0.017 rows=1 loops=1)"
"Planning time: 0.653 ms"
"Execution time: 0.326 ms"

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

Re: [PERFORM] Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher

[Jim Nasby]

On 9/19/16 2:29 AM, Knels, Udo wrote:

The difference is that V9.1 uses Nested Loop and the index table_a_pk.
V9.2 and higher don’t use the index.


First thing I'd try is running a manual ANALYZE; on the upgraded 
database; the 9.2 plan you showed seems to be using default values, so 
it thinks it's going to get 100 rows when it's only getting a few.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance