Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

On Sat, Nov 12, 2016 at 5:33 PM, otar shavadze wrote: > Tried > > OPERATOR(pg_catalog.@>) > > > as Tom mentioned, but still, don't get fast performance when value does > not existed in any array. > Did you build the correct index? > > Also "played" with many

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

Tried OPERATOR(pg_catalog.@>) as Tom mentioned, but still, don't get fast performance when value does not existed in any array. Also "played" with many different ways, gin, gist indexes (gin with and without *gin__int_ops*) but, always, there was some situation, where search in array was

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

Jeff Janes writes: > On Thu, Nov 10, 2016 at 7:11 AM, Tom Lane wrote: >> If you are using that contrib module, and it's capturing this operator >> reference, that would probably explain the bad estimate. You could >> drop the extension if you're not

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

On Thu, Nov 10, 2016 at 7:11 AM, Tom Lane wrote: > otar shavadze writes: > >> Hmmm ... actually, I wonder if maybe '@>' here is the contrib/intarray > >> operator not the core operator? The intarray operator didn't get > plugged > >> into any real

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

otar shavadze writes: >> Hmmm ... actually, I wonder if maybe '@>' here is the contrib/intarray >> operator not the core operator? The intarray operator didn't get plugged >> into any real estimation logic until 9.6. > So, you mean that better would be go to version 9.6 ?

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

@Jeff > most_common_elems. Is it empty, or is it not empty? If not empty, does > it contain the specific values you used in your queries? No, most_common_elems is not empty. it contain the specific values I used in queries. @Tom > > Hmmm ... actually, I wonder if maybe '@>' here is the

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

I wrote: > Seems like your problem here is that the planner has no idea about the > selectivity of this condition --- if it did, I think it would have > made the right choice, because it would have made a much higher estimate > for the cost of the indexscan. > AFAICT, Postgres 9.5 does make a

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

On Tue, Nov 8, 2016 at 12:27 PM, otar shavadze wrote: > > 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? > most_common_elems. Is it empty, or is it not

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

otar shavadze writes: > " -> 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[])" Seems like your problem here is that the planner has

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

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

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

On Tue, Nov 8, 2016 at 9:43 AM, otar shavadze wrote: > I have table with 500 000 rows, I have int[] column "my_array" in this > table, this array column contains minimum 1 and maximum 5 different values. > > I have GIN index on my_array column: > > * "CREATE INDEX idx ON

Re: [GENERAL] Gin indexes on intarray is fast when value in array does not exists, and slow, when value exists

On Tue, Nov 8, 2016 at 8:43 PM, otar shavadze wrote: > I have table with 500 000 rows, I have int[] column "my_array" in this > table, this array column contains minimum 1 and maximum 5 different values. > you didn't show us postgres version. > > I have GIN index on

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

I have table with 500 000 rows, I have int[] column "my_array" in this table, this array column contains minimum 1 and maximum 5 different values. I have GIN index on my_array column: * "CREATE INDEX idx ON table_name USING GIN(my_array gin__int_ops)"* Then I use this query: "*SELECT * FROM

Re: [GENERAL] GIN Indexes: Extensibility

Paul, This is a really valuable idea. It will work in some situations for me. But in other situations I do not know if table will have a key of type int[] or string[] or even mixed. That’s why I’d wish to use JSON arrays and customize sort ordering. Anyway I appreciate you shared this

Re: [GENERAL] GIN Indexes: Extensibility

Vick, you are right. That’s why I’d wish to add some custom code to MY PostgreSQL instance and set such a sort order, which is optimal for my application. On Jul 27, 2016, at 17:44, Vick Khera wrote: > On Wed, Jul 27, 2016 at 3:28 AM, Anton Ananich

Re: [GENERAL] GIN Indexes: Extensibility

On 07/27/2016 07:44 AM, Vick Khera wrote: On Wed, Jul 27, 2016 at 3:28 AM, Anton Ananich wrote: In my situation this order is invalid. Obviously, year 2016 should go after 2014, like that: I think you expect JSONB to sort differently than it does. I cannot imagine

Re: [GENERAL] GIN Indexes: Extensibility

On Wed, Jul 27, 2016 at 3:28 AM, Anton Ananich wrote: > In my situation this order is invalid. Obviously, year 2016 should go after > 2014, like that: I think you expect JSONB to sort differently than it does. I cannot imagine what a "natural" ordering of arbitrary JSON

[GENERAL] GIN Indexes: Extensibility

Dear All, Here is what I have: user=# create table FOO (key jsonb); CREATE TABLE user=# insert into FOO(key) values ('[2014]'), ('[2015]'), ('[2016]'), ('[2014, 2]'), ('[2014, 2, 3]'), ('[2014, 3]'), ('[2014,2,4]'), ('[2014, 2,4]'), ('[2014,3,13]'), ('[2014, 2, 15]'); INSERT 0 10 user=# SELECT

Re: [GENERAL] GiN indexes

On Tue, 31 Aug 2010, Stavroula Gasparotto wrote: Currently, only the B-tree, GiST and GIN index types support multicolumn indexes. What does this mean exactly if I'm trying to create a multicolumn GIN index? Does this mean the index can contain one or more tsvector type fields only, or can I