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?
-- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general