Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

From: otar shavadze <oshavadze(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists
Date: 2016-11-08 20:27:47
Message-ID: CAG-jOyBXchywhAgEkG8kY2YSPW7w7GxAu8asQogUVD-Dv0viBw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I increased rows limit from 50 to 500, because now, difference visible much
better, so query is:

explain analyze *SELECT * FROM table_name WHERE my_array @>
'{x}'::integer[] ORDER BY id desc LIMIT 500*

with GIN index:

"Limit (cost=107.83..109.08 rows=500 width=905) (actual
time=978.256..978.293 rows=500 loops=1)"
" -> Sort (cost=107.83..109.16 rows=533 width=905) (actual
time=978.254..978.272 rows=500 loops=1)"
" Sort Key: id DESC"
" Sort Method: top-N heapsort Memory: 589kB"
" -> Bitmap Heap Scan on table_name (cost=23.93..83.69 rows=533
width=905) (actual time=50.612..917.422 rows=90049 loops=1)"
" Recheck Cond: (my_array @> '{8}'::integer[])"
" Heap Blocks: exact=46525"
" -> Bitmap Index Scan on idx (cost=0.00..23.80 rows=533
width=0) (actual time=35.054..35.054 rows=90052 loops=1)"
" Index Cond: (my_array @> '{8}'::integer[])"
"Planning time: 0.202 ms"
"Execution time: 978.718 ms"

Without index:

"Limit (cost=7723.12..7724.37 rows=500 width=122) (actual
time=184.041..184.102 rows=500 loops=1)"
" -> Sort (cost=7723.12..7724.45 rows=534 width=122) (actual
time=184.039..184.052 rows=500 loops=1)"
" Sort Key: id DESC"
" Sort Method: top-N heapsort Memory: 157kB"
" -> Seq Scan on table_name (cost=0.00..7698.93 rows=534 width=122)
(actual time=0.020..176.079 rows=84006 loops=1)"
" Filter: (my_array @> '{14}'::integer[])"
" Rows Removed by Filter: 450230"
"Planning time: 0.165 ms"
"Execution time: 184.155 ms"

Postgres version: 9.5; OS: Windows 7; RAM: 8GB

In picture is some config current values.

p.s. In "pg_stats" really many values (long lists in "most_common_vals",
"most_common_freqs") and in another columns
Which one columns should I show you? All?

Attachment Content-Type Size
image/png 23.3 KB

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2016-11-08 20:28:00 Re: Best practices to manage custom statistics
Previous Message Adrian Klaver 2016-11-08 20:20:09 Re: resolution order for foreign key actions?