Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On Tue, Jun 7, 2016 at 10:03 PM, Josh Berkus wrote: > On 06/07/2016 08:42 AM, Nicolas Paris wrote: >> You have to do something different. Using multiple columns and/or >> multiple rows might we workable. >> >> >> Certainly. Kind of disappointing, because I won't find

Re: [PERFORM] Performance of LIKE/NOT LIKE when used in single query

On Wednesday, June 8, 2016, Ed Felstein wrote: > Hello, > First time poster here. Bear with me. > Using PostgreSQL 9.5 > I have a situation where I have a LIKE and a NOT LIKE in the same query to > identify strings in a varchar field. Since I am using wildcards, I have >

[PERFORM] Performance of LIKE/NOT LIKE when used in single query

Hello, First time poster here. Bear with me. Using PostgreSQL 9.5 I have a situation where I have a LIKE and a NOT LIKE in the same query to identify strings in a varchar field. Since I am using wildcards, I have created a GIN index on the field in question, which makes LIKE '%%' searches

Re: [PERFORM] 9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6

UP. repeat tests on local vm.. reults are discouraging OS PG TPS AVG latency Centos 79.5.3 23.711023 168.421 Centos 79.5.3 26.609271 150.188 Centos 79.5.3 25.220044 158.416 Centos 79.5.3 25.598977 156.047

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

2016-06-07 15:03 GMT+02:00 Josh Berkus : > On 06/07/2016 08:42 AM, Nicolas Paris wrote: > > ​​You have to do something different. Using multiple columns and/or > > multiple rows might we workable. > ​Getting a unique document from multiple rows coming from postgresql

Re: [PERFORM] Combination of partial and full indexes

I dont think offers_source_id_o_key_idx will be used at all. It is a UNIQUE index on (source_id, o_key), but your query does not filter for any "o_key", so reading that index does not provide the pointers needed to fetch the actual data in the table. I will try an index on source_id,

[PERFORM] Combination of partial and full indexes

Hi. I had a fight with a query planner because it doesn’t listen. There are two indexes: - with expression in descending order: "offers_offer_next_update_idx" btree (offer_next_update(update_ts, update_freq) DESC) WHERE o_archived = false - unique with two columns:

Re: [PERFORM] Locking concurrency: select for update vs update

Thanks after your description I found select name from phone_number_type WHERE id_phone_number_type=4 for *NO KEY* update (Postgresql 9.3 ) W dniu 2016-06-07 o 15:24, Tom Lane pisze: Streamsoft - Mirek Szajowski writes: Why I can't execute 'select for update'

Re: [PERFORM] Locking concurrency: select for update vs update

Streamsoft - Mirek Szajowski writes: > Why I can't execute 'select for update' but I can update? In recent PG versions, the lock held due to having inserted an FK dependent row effectively only locks the key fields of the parent row. UPDATE can tell whether you're

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On 06/07/2016 08:42 AM, Nicolas Paris wrote: > ​​You have to do something different. Using multiple columns and/or > multiple rows might we workable. > > > ​Certainly. Kind of disappointing, because I won't find any json builder > as performant as postgresql.​ That's nice to hear. >

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On Tue, Jun 7, 2016 at 8:42 AM, Nicolas Paris wrote: > > > 2016-06-07 14:39 GMT+02:00 David G. Johnston : > >> On Tue, Jun 7, 2016 at 8:36 AM, Nicolas Paris >> wrote: >> >>> 2016-06-07 14:31 GMT+02:00 David G. Johnston

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

2016-06-07 14:39 GMT+02:00 David G. Johnston : > On Tue, Jun 7, 2016 at 8:36 AM, Nicolas Paris wrote: > >> 2016-06-07 14:31 GMT+02:00 David G. Johnston >> : >> >>> On Tue, Jun 7, 2016 at 7:44 AM, Nicolas Paris

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On Tue, Jun 7, 2016 at 8:36 AM, Nicolas Paris wrote: > 2016-06-07 14:31 GMT+02:00 David G. Johnston : > >> On Tue, Jun 7, 2016 at 7:44 AM, Nicolas Paris >> wrote: >> >>> Hello, >>> >>> I run a query transforming huge tables

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

2016-06-07 14:31 GMT+02:00 David G. Johnston : > On Tue, Jun 7, 2016 at 7:44 AM, Nicolas Paris wrote: > >> Hello, >> >> I run a query transforming huge tables to a json document based on a period. >> It works great for a modest period (little

Re: [PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

On Tue, Jun 7, 2016 at 7:44 AM, Nicolas Paris wrote: > Hello, > > I run a query transforming huge tables to a json document based on a period. > It works great for a modest period (little dataset). > However, when increasing the period (huge dataset) I get this error: > >

[PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

Hello, I run a query transforming huge tables to a json document based on a period. It works great for a modest period (little dataset). However, when increasing the period (huge dataset) I get this error: SQL ERROR[54000] ERROR: array size exceeds the maximum allowed (1073741823) Thanks by

Re: [PERFORM] Locking concurrency: select for update vs update

It means that second TX hangs/wait on this sql code FIRST TX INSERT INTO phone_number( id_phone_number,id_phone_number_type) VALUES (1,500); SECOND TX select * from phone_number_type WHERE id_phone_number_type=500 for update //hangs/wait to TX with insert into ends but this works

Re: [PERFORM] Locking concurrency: select for update vs update

On 7 June 2016 at 09:31, Streamsoft - Mirek Szajowski < m.szajow...@streamsoft.pl> wrote: > Hello, > > I have two tables phone_number and phone_number_type > > When I start transaction and insert phone_number using FK from > phone_number_type. Then I can during another TX update row from >

[PERFORM] Locking concurrency: select for update vs update

Hello, I have two tables phone_number and phone_number_type When I start transaction and insert phone_number using FK from phone_number_type. Then I can during another TX update row from phone_number_type, but I can't execute select for update on it. In db stats I see during inserInto