[PERFORM] Locking concurrency: select for update vs update

2016-06-07 Thread Streamsoft - Mirek Szajowski 

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 AccessShareLock, during update 
RowExclusieLock but during select for update AccessExclusieLock.


Why I can't execute 'select for update' but I can update We often 
use 'select for update' to avoid update the same record in differents TX 
but I don't understand why this block another tx from using this record 
as FK



Best regards
Mirek


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

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

2016-06-07 Thread Szymon Lipiński 
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
> phone_number_type, but I can't execute select for update on it.
>
> In db stats I see during inserInto AccessShareLock, during update
> RowExclusieLock but during select for update AccessExclusieLock.
>
> Why I can't execute 'select for update' but I can update We often use
> 'select for update' to avoid update the same record in differents TX but I
> don't understand why this block another tx from using this record as FK
>
>
> Best regards
> Mirek
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

What do you mean by " can't execute select for update on it"? Can you show
an example code, and the error you get?

-- 
regards Szymon Lipiński

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

2016-06-07 Thread Streamsoft - Mirek Szajowski 

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 fine

  UPDATE phone_number_type SET val=val+1 WHERE id_phone_number_type=500

W dniu 2016-06-07 o 09:35, Szymon Lipiński pisze:



On 7 June 2016 at 09:31, Streamsoft - Mirek Szajowski 
mailto: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
phone_number_type, but I can't execute select for update on it.

In db stats I see during inserInto AccessShareLock, during update
RowExclusieLock but during select for update AccessExclusieLock.

Why I can't execute 'select for update' but I can update We
often use 'select for update' to avoid update the same record in
differents TX but I don't understand why this block another tx
from using this record as FK


Best regards
Mirek


-- 
Sent via pgsql-performance mailing list

(pgsql-performance@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


What do you mean by " can't execute select for update on it"? Can you 
show an example code, and the error you get?


--
regards Szymon Lipiński


--

z poważaniem

*Mirek Szajowski*
Projektant-programista
Tel: 663 762 690
m.szajow...@streamsoft.pl 


*Streamsoft*
65-140 Zielona Góra, ul.Kossaka 10
NIP: 929-010-00-96, REGON: 970033184
Tel: +48 68 45 66 900, Fax: +48 68 45 66 933
www.streamsoft.pl 

*Uwaga: * Treść niniejszej wiadomości może być poufna i objęta zakazem 
jej ujawniania. Jeśli czytelnik lub odbiorca niniejszej wiadomości nie 
jest jej zamierzonym adresatem, pracownikiem lub pośrednikiem 
upoważnionym do jej przekazania adresatowi, niniejszym informujemy że 
wszelkie rozprowadzanie, dystrybucja lub powielanie niniejszej 
wiadomości jest zabronione. Odbiorca lub czytelnik korespondencji, który 
otrzymał ja omyłkowo, proszony jest o zawiadomienie nadawcy i usuniecie 
tego materiału z komputera. Dziękujemy. Streamsoft.


*Note: * The information contained in this message may be privileged and 
confidential and protected from disclosure. If the reader or receiver of 
this message is not the intended recipient, or an employee or agent 
responsible for delivering this message to the intended recipient, you 
are hereby notified that any dissemination, distribution or copying of 
this communication is strictly prohibited. If you received this in 
error, please contact the sender and delete the material from any 
computer. Thank you. Streamsoft.

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

2016-06-07 Thread Nicolas Paris 
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 advance,

Informations:

postgresql 9.4
shared_buffers = 55GB
64bit Red Hat Enterprise Linux Server release 6.7

the query:
 WITH sel AS
  (SELECT ids_pat,
  ids_nda
   FROM eds.nda
   WHERE (dt_deb_nda >= '20150101'
  AND dt_deb_nda <= '20150401')),
  diag AS
  ( SELECT ids_nda_rum,
   json_agg(diago) AS diago,
   count(1) AS total
   FROM
 (SELECT ids_nda_rum,
 json_build_object( 'cd_cim', cd_cim,
'lib_cim',lib_typ_diag_tr, 'dt_cim',dt_exec) AS diago
  FROM eds.fait_diag_tr
  WHERE ids_nda IN
  (SELECT ids_nda
   FROM sel)
  ORDER BY dt_exec) AS diago2
   GROUP BY ids_nda_rum),
  act AS
  ( SELECT ids_nda_rum,
   json_agg(acto) AS acto,
   count(1) AS total
   FROM
 ( SELECT ids_nda_rum,
  json_build_object( 'cd_act',cd_ccam, 'dt_act',dt_exec) AS acto
  FROM eds.fait_act_tr
  WHERE ids_nda IN
  (SELECT ids_nda
   FROM sel)
  ORDER BY dt_exec) AS acto2
   GROUP BY ids_nda_rum ),
  ghm AS
  ( SELECT ids_nda_rum,
   json_agg(ghmo) AS ghmo,
   count(1) AS total
   FROM
 ( SELECT ids_nda_rum,
  json_build_object( 'cd_ghm',cd_ghm, 'cd_ghs',cd_ghs,
'status',lib_statut_tr, 'dt_maj_rum_ghm',dt_maj_rum_ghm) AS ghmo
  FROM eds.nda_rum_ghm_tr
  LEFT JOIN eds.nda_rum_tr rum USING (ids_nda_rum)
  WHERE nda_rum_ghm_tr.ids_nda IN
  (SELECT ids_nda
   FROM sel)
AND rum.cd_rum = 'RSS'
  ORDER BY dt_maj_rum_ghm) AS ghmo
   GROUP BY ids_nda_rum ),
  lab AS
  (SELECT ids_nda,
  json_agg(lab) AS labo,
  count(1) AS total
   FROM
 (SELECT ids_nda,
 json_build_object( 'valeur_type_tr',valeur_type_tr,
'dt_fait', dt_fait, 'unite',unite, 'cd_test_lab',cd_test_lab,
'valeur_sign_tr',valeur_sign_tr, 'valeur_num_tr',valeur_num_tr,
'valeur_text_tr',valeur_text_tr,
'valeur_abnormal_tr',valeur_abnormal_tr) AS lab
  FROM eds.fait_lab_tr
  WHERE ids_nda IN
  (SELECT ids_nda
   FROM sel)
  ORDER BY dt_fait) AS labo
   GROUP BY ids_nda),
  rum AS
  ( SELECT ids_nda,
   json_agg(rum) AS rumo,
   count(1) AS total
   FROM
 ( SELECT ids_nda,
  json_build_object( 'cd_rum',cd_rum, 'dt_deb_rum',
dt_deb_rum, 'dt_fin_rum', dt_fin_rum, 'diag',
json_build_object('total',diag.total,'diag',diag.diago), 'act',
json_build_object('total',act.total,'act',act.acto) ) AS rum
  FROM eds.nda_rum_tr
  LEFT JOIN diag USING (ids_nda_rum)
  LEFT JOIN act USING (ids_nda_rum)
  WHERE ids_nda IN
  (SELECT ids_nda
   FROM sel)
AND cd_rum = 'RUM' ) AS rumo
   GROUP BY ids_nda),
  rss AS
  ( SELECT ids_nda,
   json_agg(rss) AS rsso,
   count(1) AS total
   FROM
 ( SELECT ids_nda,
  json_build_object( 'cd_rum',cd_rum, 'dt_deb_rss',
dt_deb_rum, 'dt_fin_rss', dt_fin_rum, 'ghm',
json_build_object('total',ghm.total,'ghm',ghm.ghmo), 'rum',
json_build_object('total',rum.total, 'rum',rum.rumo) ) AS rss
  FROM eds.nda_rum_tr
  LEFT JOIN ghm USING (ids_nda_rum)
  LEFT JOIN rum USING (ids_nda)
  WHERE ids_nda IN
  (SELECT ids_nda
   FROM sel)
AND cd_rum = 'RSS' ) AS rss
   GROUP BY ids_nda),
  enc AS
  (SELECT 'Encounter' AS "resourceType",
  cd_nda AS "identifier",
  duree_hospit AS "length",
  lib_statut_nda_tr AS "status",
  lib_type_nda_tr AS "type",
  ids_pat,
  json_build_object('start', dt_deb_nda,'end', dt_fin_nda) AS
"appointment",
  json_build_object('total',lab.total, 'lab',lab.labo) AS lab,
  json_build_object('total',rss.total, 'rss',rss.rsso) AS rss
   FROM eds.nda_tr
   LEFT JOIN lab USING (ids_nda)
   LEFT JOIN rss USING (ids_nda)
   WHERE ids_nda IN
   (SELECT ids_nda
FROM sel)
   ORDER BY dt_deb_nda ASC)
SELECT 'Bundle' AS "resourceType",
   count(1) AS total,
   array_to_json(array_agg(ROW)) AS encounter
FROM
  (SELECT 'Patient' AS "resourceType",
  ipp AS "identifier",
  nom AS "name",
  cd_sex_tr AS "gender",
  dt_nais AS "birthDate",
  json_build_array(enc.*) AS encounters
   FROM eds.patient_tr
   INNER JOIN enc USING (ids_pat) ) ROW;

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

2016-06-07 Thread 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 dataset).
> However, when increasing the period (huge dataset) I get this error:
>
> SQL ERROR[54000]
> ERROR: array size exceeds the maximum allowed (1073741823)
>
> ​https://www.postgresql.org/about/​

​Maximum Field Size: 1 GB​

​It doesn't matter that the data never actually is placed into a physical
table.

David J.

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

2016-06-07 Thread Nicolas Paris 
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 dataset).
>> However, when increasing the period (huge dataset) I get this error:
>>
>> SQL ERROR[54000]
>> ERROR: array size exceeds the maximum allowed (1073741823)
>>
>> ​https://www.postgresql.org/about/​
>
> ​Maximum Field Size: 1 GB​
>

It means a json cannot exceed 1GB in postgresql, right ?
Then I must build it with an external tool ?
​


>
> ​It doesn't matter that the data never actually is placed into a physical
> table.
>
> David J.
>
>

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

2016-06-07 Thread 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 
>> 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:
>>>
>>> SQL ERROR[54000]
>>> ERROR: array size exceeds the maximum allowed (1073741823)
>>>
>>> ​https://www.postgresql.org/about/​
>>
>> ​Maximum Field Size: 1 GB​
>>
>
> It means a json cannot exceed 1GB in postgresql, right ?
>

​Yes​


> Then I must build it with an external tool ?
> ​
>
>

​​You have to do something different.  Using multiple columns and/or
multiple rows might we workable.

David J.

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

2016-06-07 Thread Nicolas Paris 
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 
>>> 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:

 SQL ERROR[54000]
 ERROR: array size exceeds the maximum allowed (1073741823)

 ​https://www.postgresql.org/about/​
>>>
>>> ​Maximum Field Size: 1 GB​
>>>
>>
>> It means a json cannot exceed 1GB in postgresql, right ?
>>
>
> ​Yes​
>
>
>> Then I must build it with an external tool ?
>> ​
>>
>>
>
> ​​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.​

​

Will this 1GO restriction is supposed to increase in a near future ?​


> David J.
>
>

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

2016-06-07 Thread David G. Johnston 
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 >> >:
>>>
 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:
>
> SQL ERROR[54000]
> ERROR: array size exceeds the maximum allowed (1073741823)
>
> ​https://www.postgresql.org/about/​

 ​Maximum Field Size: 1 GB​

>>>
>>> It means a json cannot exceed 1GB in postgresql, right ?
>>>
>>
>> ​Yes​
>>
>>
>>> Then I must build it with an external tool ?
>>> ​
>>>
>>>
>>
>> ​​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.​
>
> ​
>
> Will this 1GO restriction is supposed to increase in a near future ?​
>
>
There has been zero chatter on the public lists about increasing any of the
limits on that page I linked to.

David J.
​

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

2016-06-07 Thread 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.
> 
> 
> ​Certainly. Kind of disappointing, because I won't find any json builder
> as performant as postgresql.​

That's nice to hear.

> Will this 1GO restriction is supposed to increase in a near future ?​

Not planned, no.  Thing is, that's the limit for a field in general, not
just JSON; changing it would be a fairly large patch.  It's desireable,
but AFAIK nobody is working on it.

-- 
--
Josh Berkus
Red Hat OSAS
(any opinions are my own)


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

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

2016-06-07 Thread Tom Lane 
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 trying to change the row's key fields,
and it will proceed if you aren't.  SELECT FOR UPDATE has to lock the
whole row (since it must assume you might be intending to change any
fields of the row); so it blocks until the FK lock goes away.

regards, tom lane


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

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

2016-06-07 Thread Streamsoft - Mirek Szajowski 

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' 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 trying to change the row's key fields,
and it will proceed if you aren't.  SELECT FOR UPDATE has to lock the
whole row (since it must assume you might be intending to change any
fields of the row); so it blocks until the FK lock goes away.

regards, tom lane

[PERFORM] Combination of partial and full indexes

2016-06-07 Thread Rafał Gutkowski 
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:
"offers_source_id_o_key_idx" UNIQUE, btree (source_id, o_key)

Here's the query with filter for offers.source_id columns which
is pretty slow because "offers_source_id_o_key_idx" is not used:

EXPLAIN ANALYZE
SELECT offers.o_url AS offers_o_url
FROM offers
WHERE offers.source_id = 1 AND offers.o_archived = false AND now() > 
offer_next_update(offers.update_ts, offers.update_freq)
ORDER BY offer_next_update(offers.update_ts, offers.update_freq) DESC
LIMIT 1000;

Limit  (cost=0.68..23403.77 rows=1000 width=116) (actual 
time=143.544..147.870 rows=1000 loops=1)
  ->  Index Scan using offers_offer_next_update_idx on offers  
(cost=0.68..1017824.69 rows=43491 width=116) (actual time=143.542..147.615 
rows=1000 loops=1)
Index Cond: (now() > offer_next_update(update_ts, update_freq))
Filter: (source_id = 1)
Rows Removed by Filter: 121376
Total runtime: 148.023 ms


When I remove filter on offers.source_id, query plan looks like this:

EXPLAIN ANALYZE
SELECT offers.o_url AS offers_o_url
FROM offers
WHERE offers.o_archived = false AND now() > 
offer_next_update(offers.update_ts, offers.update_freq)
ORDER BY offer_next_update(offers.update_ts, offers.update_freq) DESC
LIMIT 1000;

Limit  (cost=0.68..4238.27 rows=1000 width=116) (actual time=0.060..3.877 
rows=1000 loops=1)
  ->  Index Scan using offers_offer_next_update_idx on offers  
(cost=0.68..1069411.78 rows=252363 width=116) (actual time=0.058..3.577 
rows=1000 loops=1)
Index Cond: (now() > offer_next_update(update_ts, update_freq))
Total runtime: 4.031 ms


I even tried to change orders of conditions in second query but it doesn't seem
to make a difference for a planner.

Shouldn't query planner use offers_source_id_o_key_idx to speed up query above?


PostgreSQL version: PostgreSQL 9.3.12 on x86_64-unknown-linux-gnu, compiled by 
gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit

Configuration:
 name |current_setting |
source
--++--
 application_name | psql   | client
 checkpoint_completion_target | 0.9| 
configuration file
 checkpoint_segments  | 3  | 
configuration file
 client_encoding  | UTF8   | client
 DateStyle| ISO, MDY   | 
configuration file
 default_text_search_config   | pg_catalog.english | 
configuration file
 effective_cache_size | 128MB  | 
configuration file
 external_pid_file| /var/run/postgresql/9.3-main.pid   | 
configuration file
 lc_messages  | en_US.UTF-8| 
configuration file
 lc_monetary  | en_US.UTF-8| 
configuration file
 lc_numeric   | en_US.UTF-8| 
configuration file
 lc_time  | en_US.UTF-8| 
configuration file
 max_connections  | 100| 
configuration file
 max_locks_per_transaction| 168| 
configuration file
 max_stack_depth  | 2MB| 
environment variable
 port | 5432   | 
configuration file
 shared_buffers   | 4GB| 
configuration file
 temp_buffers | 12MB   | 
configuration file
 unix_socket_directories  | /var/run/postgresql| 
configuration file
 work_mem | 16MB   | 
configuration file


Definitions:

CREATE OR REPLACE FUNCTION public.offer_next_update(last timestamp without time 
zone, minutes smallint)
 RETURNS timestamp without time zone
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
BEGIN
RETURN last + (minutes || ' min')::interval;
END
$function$

Re: [PERFORM] Combination of partial and full indexes

2016-06-07 Thread Gerardo Herzig 
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, offer_next_update(offers.update_ts, 
offers.update_freq) and see what happens

HTH
Gerardo

- Mensaje original -
> De: "Rafał Gutkowski" 
> Para: pgsql-performance@postgresql.org
> Enviados: Martes, 7 de Junio 2016 10:39:14
> Asunto: [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:
> "offers_source_id_o_key_idx" UNIQUE, btree (source_id, o_key)
> 
> 
> Here's the query with filter for offers.source_id columns which
> is pretty slow because "offers_source_id_o_key_idx" is not used:
> 
> 
> EXPLAIN ANALYZE
> SELECT offers.o_url AS offers_o_url
> FROM offers
> WHERE offers.source_id = 1 AND offers.o_archived = false AND now() >
> offer_next_update(offers.update_ts, offers.update_freq)
> ORDER BY offer_next_update(offers.update_ts, offers.update_freq) DESC
> LIMIT 1000;
> 
> 
> Limit (cost=0.68..23403.77 rows=1000 width=116) (actual
> time=143.544..147.870 rows=1000 loops=1)
> -> Index Scan using offers_offer_next_update_idx on offers
> (cost=0.68..1017824.69 rows=43491 width=116) (actual
> time=143.542..147.615 rows=1000 loops=1)
> Index Cond: (now() > offer_next_update(update_ts, update_freq))
> Filter: (source_id = 1)
> Rows Removed by Filter: 121376
> Total runtime: 148.023 ms
> 
> 
> 
> 
> When I remove filter on offers.source_id, query plan looks like this:
> 
> 
> EXPLAIN ANALYZE
> SELECT offers.o_url AS offers_o_url
> FROM offers
> WHERE offers.o_archived = false AND now() >
> offer_next_update(offers.update_ts, offers.update_freq)
> ORDER BY offer_next_update(offers.update_ts, offers.update_freq) DESC
> LIMIT 1000;
> 
> 
> Limit (cost=0.68..4238.27 rows=1000 width=116) (actual
> time=0.060..3.877 rows=1000 loops=1)
> -> Index Scan using offers_offer_next_update_idx on offers
> (cost=0.68..1069411.78 rows=252363 width=116) (actual
> time=0.058..3.577 rows=1000 loops=1)
> Index Cond: (now() > offer_next_update(update_ts, update_freq))
> Total runtime: 4.031 ms
> 
> 
> 
> 
> I even tried to change orders of conditions in second query but it
> doesn't seem
> to make a difference for a planner.
> 
> 
> Shouldn't query planner use offers_source_id_o_key_idx to speed up
> query above?
> 
> 
> 
> 
> PostgreSQL version: PostgreSQL 9.3.12 on x86_64-unknown-linux-gnu,
> compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8.4, 64-bit
> 
> 
> Configuration:
> name | current_setting | source
> --++--
> application_name | psql | client
> checkpoint_completion_target | 0.9 | configuration file
> checkpoint_segments | 3 | configuration file
> client_encoding | UTF8 | client
> DateStyle | ISO, MDY | configuration file
> default_text_search_config | pg_catalog.english | configuration file
> effective_cache_size | 128MB | configuration file
> external_pid_file | /var/run/postgresql/9.3-main.pid | configuration
> file
> lc_messages | en_US.UTF-8 | configuration file
> lc_monetary | en_US.UTF-8 | configuration file
> lc_numeric | en_US.UTF-8 | configuration file
> lc_time | en_US.UTF-8 | configuration file
> max_connections | 100 | configuration file
> max_locks_per_transaction | 168 | configuration file
> max_stack_depth | 2MB | environment variable
> port | 5432 | configuration file
> shared_buffers | 4GB | configuration file
> temp_buffers | 12MB | configuration file
> unix_socket_directories | /var/run/postgresql | configuration file
> work_mem | 16MB | configuration file
> 
> 
> 
> 
> Definitions:
> 
> 
> 
> CREATE OR REPLACE FUNCTION public.offer_next_update(last timestamp
> without time zone, minutes smallint)
> RETURNS timestamp without time zone
> LANGUAGE plpgsql
> IMMUTABLE
> AS $function$
> BEGIN
> RETURN last + (minutes || ' min')::interval;
> END
> $function$
> 
> 
> 
> 


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

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

2016-06-07 Thread Nicolas Paris 
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 is not
that easy... The external tools considers each postgresql JSON fields as
strings or have to parse it again. Parsing them would add an overhead on
the external tool, and I d'say this would be better to build the entire
JSON in the external tool. This leads not to use postgresql JSON builder at
all, and delegate this job to a tool that is able to deal with > 1GO
documents.



> >
> >
> > ​Certainly. Kind of disappointing, because I won't find any json builder
> > as performant as postgresql.​
>
> That's nice to hear.
>
> > Will this 1GO restriction is supposed to increase in a near future ?​
>
> Not planned, no.  Thing is, that's the limit for a field in general, not
> just JSON; changing it would be a fairly large patch.  It's desireable,
> but AFAIK nobody is working on it.
>

Comparing to mongoDB 16MO document limitation 1GO is great (
http://tech.tulentsev.com/2014/02/limitations-of-mongodb/)​. But for my use
case this is not sufficient.



> --
> --
> Josh Berkus
> Red Hat OSAS
> (any opinions are my own)
>

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

2016-06-07 Thread Антон Бушмелев 
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
Centos 79.4.8   278.572191  14.077
Centos 79.4.8   247.237755  16.177
Centos 79.4.8   240.007524  16.276
Centos 79.4.8   237.862238  16.596

ps: latest update on centos 7 +xfs + lates database version from repo, no 
pgbouncer 


> On 25 May 2016, at 17:33, Vladimir Borodin  wrote:
> 
> Hi all.
> 
> We have found that queries through PgBouncer 1.7.2 (with transaction pooling) 
> to local PostgreSQL are almost two times slower in 9.5.3 than in 9.4.8 on 
> RHEL 6 hosts (all packages are updated to last versions). Meanwhile the 
> problem can’t be reproduced i.e. on Ubuntu 14.04 (also fully-updated).
> 
> Here is how the results look like for 9.4, 9.5 and 9.6. All are built from 
> latest commits on yesterday in
>   * REL9_4_STABLE (a0cc89a28141595d888d8aba43163d58a1578bfb),
>   * REL9_5_STABLE (e504d915bbf352ecfc4ed335af934e799bf01053),
>   * master (6ee7fb8244560b7a3f224784b8ad2351107fa55d).
> 
> All of them are build on the host where testing is done (with stock gcc 
> versions). Sysctls, pgbouncer config and everything we found are the same, 
> postgres configs are default, PGDATA is in tmpfs. All numbers are 
> reproducible, they are stable between runs.
> 
> Shortly:
> 
> OSPostgreSQL version  TPS Avg. 
> latency
> RHEL 69.4 44898   
> 1.425 ms
> RHEL 69.5 26199   
> 2.443 ms
> RHEL 69.5 43027   
> 1.487 ms
> Ubuntu 14.04  9.4 67458   0.949 ms
> Ubuntu 14.04  9.5 64065   0.999 ms
> Ubuntu 14.04  9.6 64350   0.995 ms
> 
> You could see that the difference between major versions on Ubuntu is not 
> significant, but on RHEL 9.5 is 70% slower than 9.4 and 9.6.
> 
> Below are more details.
> 
> RHEL 6:
> 
> postgres@pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 
> -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg94'
> transaction type: SELECT only
> scaling factor: 100
> query mode: simple
> number of clients: 64
> number of threads: 64
> duration: 60 s
> number of transactions actually processed: 2693962
> latency average: 1.425 ms
> tps = 44897.461518 (including connections establishing)
> tps = 44898.763258 (excluding connections establishing)
> postgres@pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 
> -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg95'
> transaction type: SELECT only
> scaling factor: 100
> query mode: simple
> number of clients: 64
> number of threads: 64
> duration: 60 s
> number of transactions actually processed: 1572014
> latency average: 2.443 ms
> tps = 26198.928627 (including connections establishing)
> tps = 26199.803363 (excluding connections establishing)
> postgres@pgload05g ~ $ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 60 
> -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg96'
> transaction type: SELECT only
> scaling factor: 100
> query mode: simple
> number of clients: 64
> number of threads: 64
> duration: 60 s
> number of transactions actually processed: 2581645
> latency average: 1.487 ms
> tps = 43025.676995 (including connections establishing)
> tps = 43027.038275 (excluding connections establishing)
> postgres@pgload05g ~ $
> 
> Ubuntu 14.04 (the same hardware):
> 
> postgres@pgloadpublic02:~$ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 
> 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg94'
> transaction type: SELECT only
> scaling factor: 100
> query mode: simple
> number of clients: 64
> number of threads: 64
> duration: 60 s
> number of transactions actually processed: 4047653
> latency average: 0.949 ms
> tps = 67458.361515 (including connections establishing)
> tps = 67459.983480 (excluding connections establishing)
> postgres@pgloadpublic02:~$ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 
> 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg95'
> transaction type: SELECT only
> scaling factor: 100
> query mode: simple
> number of clients: 64
> number of threads: 64
> duration: 60 s
> number of transactions actually processed: 3844084
> latency average: 0.999 ms
> tps = 64065.447458 (including connections establishing)
> tps = 64066.943627 (excluding connections establishing)
> postgres@pgloadpublic02:~$ /usr/lib/postgresql/9.4/bin/pgbench -U postgres -T 
> 60 -j 64 -c 64 -S -n 'host=localhost port=6432 dbname=pg96'
> transaction type: SELECT only
> scaling factor: 100
>

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

2016-06-07 Thread Ed Felstein 
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 run very fast.  The problem is the NOT LIKE phrases, which (as
would be expected) force a sequential scan.  Being that we're talking about
millions of records, this is not desirable.
Here's the question...
Is there a way, *using a single query*, to emulate the process of running
the LIKE part first, then running the NOT LIKE just on those results?  I
can accomplish this in a multi-step process by separating the single query
into two queries, populating a temporary table with the results of the
LIKEs, then running the NOT LIKEs on the temporary table.  For various
reasons, this is not the ideal solution for me.
Or is there another approach that would accomplish the same thing with the
same level of performance?

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

2016-06-07 Thread David G. Johnston 
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
> created a GIN index on the field in question, which makes LIKE '%%'
> searches run very fast.  The problem is the NOT LIKE phrases, which (as
> would be expected) force a sequential scan.  Being that we're talking about
> millions of records, this is not desirable.
> Here's the question...
> Is there a way, *using a single query*, to emulate the process of running
> the LIKE part first, then running the NOT LIKE just on those results?  I
> can accomplish this in a multi-step process by separating the single query
> into two queries, populating a temporary table with the results of the
> LIKEs, then running the NOT LIKEs on the temporary table.  For various
> reasons, this is not the ideal solution for me.
> Or is there another approach that would accomplish the same thing with the
> same level of performance?
>


Try AND...where col like '' and col not like ''

Or a CTE (with)

With likeqry as ( select where like )
Select from likeqry where not like

(sorry for brevity but not at a pc)

David J.

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

2016-06-07 Thread Michael Paquier 
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 any json builder
>> as performant as postgresql.
>
> That's nice to hear.
>
>> Will this 1GO restriction is supposed to increase in a near future ?
>
> Not planned, no.  Thing is, that's the limit for a field in general, not
> just JSON; changing it would be a fairly large patch.  It's desireable,
> but AFAIK nobody is working on it.

And there are other things to consider on top of that, like the
maximum allocation size for palloc, the maximum query string size,
COPY, etc. This is no small project, and the potential side-effects
should not be underestimated.
-- 
Michael


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

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

2016-06-07 Thread Tom Lane 
Michael Paquier  writes:
> On Tue, Jun 7, 2016 at 10:03 PM, Josh Berkus  wrote:
>> On 06/07/2016 08:42 AM, Nicolas Paris wrote:
>>> Will this 1GO restriction is supposed to increase in a near future ?

>> Not planned, no.  Thing is, that's the limit for a field in general, not
>> just JSON; changing it would be a fairly large patch.  It's desireable,
>> but AFAIK nobody is working on it.

> And there are other things to consider on top of that, like the
> maximum allocation size for palloc, the maximum query string size,
> COPY, etc. This is no small project, and the potential side-effects
> should not be underestimated.

It's also fair to doubt that client-side code would "just work" with
no functionality or performance problems for such large values.

I await with interest the OP's results on other JSON processors that
have no issues with GB-sized JSON strings.

regards, tom lane


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