Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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: post config pic.png
Description: PNG image

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux