From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | "Knels, Udo" <U(dot)Knels(at)treubuch-it(dot)de>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Problem with performance using query with unnest after migrating from V9.1 to V9.2 and higher |
Date: | 2016-09-22 13:38:52 |
Message-ID: | CY4PR07MB287241DA8EF831A987F7DFEFDAC90@CY4PR07MB2872.namprd07.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of Knels, Udo
Sent: Thursday, September 22, 2016 8:40 AM
To: pgsql-performance(at)postgresql(dot)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('{5010010000}'::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
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Neyman | 2016-09-22 14:32:53 | Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause |
Previous Message | Madusudanan.B.N | 2016-09-22 13:37:17 | Re: Multiple-Table-Spanning Joins with ORs in WHERE Clause |