Re: Partition pruning / agg push down for star schema in pg v11

[legrand legrand]

Adding partitioning on the dim tables, with the same keys as those used in
the fact table,
gives any star schema a good chance to use Partition Wise Join / Aggregate
plans.

Will test it soon
Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

building a server

[Gmail]

So I revisited https://momjian.us/main/writings/pgsql/hw_selection.pdf
Near the end (page 24) I spotted: 
Heavy use of server-side functions might generate significant CPU load
Just want to confirm that this referring to workload on the 
mainly-single-threaded server not an advocacy for more cores.

Thanks

PS The wiki page at https://wiki.postgresql.org/wiki/Database_Hardware lists a 
reference to a PDF by Greg Smith.  The url vectors off into some proprietary 
e-book enticement. Is that intentional?

Re: building a server

[John R Pierce]

On 12/3/2017 12:47 PM, Gmail wrote:

So I revisited https://momjian.us/main/writings/pgsql/hw_selection.pdf
Near the end (page 24) I spotted:
Heavy use of server-side functions might generate significant CPU load

Just want to confirm that this referring to workload on the 
mainly-single-threaded server not an advocacy for more cores.


well, if you have significant concurrency with high CPU usage, you want 
both more AND faster cores



--
john r pierce, recycling bits in santa cruz

Re: building a server

[Rob Sargent]

On 12/03/2017 02:15 PM, John R Pierce wrote:

On 12/3/2017 12:47 PM, Gmail wrote:

So I revisited https://momjian.us/main/writings/pgsql/hw_selection.pdf
Near the end (page 24) I spotted:
Heavy use of server-side functions might generate significant CPU load

Just want to confirm that this referring to workload on the 
mainly-single-threaded server not an advocacy for more cores.


well, if you have significant concurrency with high CPU usage, you 
want both more AND faster cores



--
john r pierce, recycling bits in santa cruz
Granted!  I suppose I'm looking for confirmation/correction on believing 
that for the server to make use of multiple cores is more dependent on 
the nature of the queries handled.  Concurrency should not be a huge 
problem for this project, though I've put pieces in place to mitigate 
that issue.

Re: building a server

[John R Pierce]

On 12/3/2017 1:39 PM, Rob Sargent wrote:
Granted! I suppose I'm looking for confirmation/correction on 
believing that for the server to make use of multiple cores is more 
dependent on the nature of the queries handled.  Concurrency should 
not be a huge problem for this project, though I've put pieces in 
place to mitigate that issue.



a single connection will only ever use more than one core if its 
explicitly doing concurrent query.



--
john r pierce, recycling bits in santa cruz

Re: building a server

[hvjunk]

> On 03 Des. 2017, at 23:39 , Rob Sargent  wrote:
> On 12/03/2017 02:15 PM, John R Pierce wrote:
>> On 12/3/2017 12:47 PM, Gmail wrote:
>>> So I revisited https://momjian.us/main/writings/pgsql/hw_selection.pdf 
>>> 
>>> Near the end (page 24) I spotted: 
>>> Heavy use of server-side functions might generate significant CPU load
>>> Just want to confirm that this referring to workload on the 
>>> mainly-single-threaded server not an advocacy for more cores.
>>> 
>> well, if you have significant concurrency with high CPU usage, you want both 
>> more AND faster cores
>> 
>> john r pierce, recycling bits in santa cruz
>> 
> Granted!  I suppose I'm looking for confirmation/correction on believing that 
> for the server to make use of multiple cores is more dependent on the nature 
> of the queries handled.  Concurrency should not be a huge problem for this 
> project, though I've put pieces in place to mitigate that issue.

I would not say that much on the “nature of the queries”, but more on the 
“number of concurrent sessions doing queries”. Granted that with the later 
postgresql versions, given you’ve set the needed to enable parallel queries and 
you have “big-enough-tables” some selects does make use of multiple cores.

As they always say: YMMV

JSON out of memory error on PostgreSQL 9.6.x

[Yuri Budilov]

Posted on Stack Overflow, sadly no replies, so trying here


CREATE TABLE X AS
SELECT json_array_elements(json_rmq -> 'orders'::text) AS order
FROM table_name
WHERE blah;

I get out of memory error.

Is there anything I can do to unpack the above?

The JSON column is about ~5 MB and it has about ~150,000 array row elements in 
'orders' above.

I tried work_mem values up to ~250MB and it did not help, the query takes about 
same time to fail.

I guess this parameter does not help JSON processing.

If there another parameter I can try? Something else?

I don't have control of the size of the JSON payload, it arrives, we store it 
in a JSON column and then we need to crack it open.

Many thanks!

Re: JSON out of memory error on PostgreSQL 9.6.x

[Tom Lane]

Yuri Budilov  writes:
> Posted on Stack Overflow, sadly no replies, so trying here
> CREATE TABLE X AS
> SELECT json_array_elements(json_rmq -> 'orders'::text) AS order
> FROM table_name
> WHERE blah;
> I get out of memory error.
> The JSON column is about ~5 MB and it has about ~150,000 array row elements 
> in 'orders' above.

I tried to reproduce that, and couldn't, given the available info.
I made a JSON value of more or less that size with

perl -e 'print "{\"orders\": [0"; for($i=1;$i<=15;$i++){print ",$i"}; print 
"]}\n";' >jsonval

and then did

regression=# create table table_name(json_rmq json);
CREATE TABLE
regression=# \copy table_name from jsonval
COPY 1
regression=# insert into table_name select * from table_name;
INSERT 0 1
regression=# insert into table_name select * from table_name;
INSERT 0 2
regression=# insert into table_name select * from table_name;
INSERT 0 4
regression=# insert into table_name select * from table_name;
INSERT 0 8
regression=# insert into table_name select * from table_name;
INSERT 0 16
regression=# insert into table_name select * from table_name;
INSERT 0 32
regression=# insert into table_name select * from table_name;
INSERT 0 64
regression=# insert into table_name select * from table_name;
INSERT 0 128
regression=# CREATE TABLE X AS
SELECT json_array_elements(json_rmq -> 'orders'::text) AS order
FROM table_name;
SELECT 38400256

Watching the process with "top", its memory consumption stayed
rock-steady.  If there's a leak in there, this example doesn't
show it.  There could be a leak related to some detail you
failed to mention, but ...

regards, tom lane

Re: JSON out of memory error on PostgreSQL 9.6.x

[rob stone]

On Sun, 2017-12-03 at 23:18 +, Yuri Budilov wrote:
> Posted on Stack Overflow, sadly no replies, so trying here
> 
>  CREATE TABLE X AS
> SELECT json_array_elements(json_rmq -> 'orders'::text) AS order
> FROM table_name
> WHERE blah;
> I get out of memory error.
> 
> Is there anything I can do to unpack the above?
> 
> The JSON column is about ~5 MB and it has about ~150,000 array
> row elements in 'orders' above.
> 
> I tried work_mem values up to ~250MB and it did not help, the query
> takes about same time to fail.
> 
> I guess this parameter does not help JSON processing.
> 
> If there another parameter I can try? Something else?
> 
> I don't have control of the size of the JSON payload, it arrives, we
> store it in a JSON column and then we need to crack it open.
> 
> Many thanks!
> 



Hello,

It would help if you advised:-
(a) version of PostgreSql being used.
(b) is column json_rmq defined as json or jsonb?
(c) OS.

Cheers,
Rob

Re: large numbers of inserts out of memory strategy

[Peter J. Holzer]

On 2017-11-30 14:27:58 -0600, Ted Toth wrote:
> On Thu, Nov 30, 2017 at 11:40 AM, Peter J. Holzer  wrote:
> > On 2017-11-30 08:43:32 -0600, Ted Toth wrote:
> >> One thing that is unclear to me is when commits occur while using psql
> >> would you know where in the docs I can find information on this
> >> subject?
> >
> > By default psql enables autocommit which causes an implicit commit after
> > every statement. With a do block I'm not sure whether that means after
> > the do block or after each statement within the do block. I'd just turn
> > autocommit off and add explicit commits wherever I wanted them.
> 
> So you think I can turn off autocommit and put BEGIN/COMMITs in a
> large file and then postmaster won't have to parse the whole thing
> when I feed it to it via psql?

No, if you still had one giant do block it would need to parse it
completely. I was thinking of the case where you have many small do
blocks, each with a few insert statements (one do block per "thing"). In
this case it would parse and execute each do block before moving on to
the next. But I wasn't sure whether the default autocommit would mean
one commit after each do block or one commit after each insert statement
(David answered this - thanks), and in any case you probably wouldn't
want to commit after each "thing", so I suggested turning autocommit off
and adding an explicit commit at the end or possibly after every nth
thing.


> > Still: Is there a reason why you use a python script to create an sql
> > script instead of directly issuing the sql queries from your python
> > script?
> 
> I already had code that generated JSON so it was relatively easy to
> add code and a cmd line arg to generate SQL instead.

Ok. that sounds like a good reason.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: JSON out of memory error on PostgreSQL 9.6.x

[Yuri Budilov]

hello good people


it is *not* JSONB, just plain JSON

the version 9.6.3 and running inside AWS RDS PostgreSQL (DBaaS)


the machine size is just 1 GB RAM and 1 CPU, is it a called "micro" size AWS 
RDS instance, we use it for DEV

we have also reproduced it on 2 CPU 8 GB RAM instance, FWIW.

It takes well under 1 min elapsed time to fail.


best regards and many thanks for trying to help me




From: rob stone 
Sent: Monday, 4 December 2017 11:01 AM
To: Yuri Budilov; John R Pierce; pgsql-general@lists.postgresql.org
Subject: Re: JSON out of memory error on PostgreSQL 9.6.x



On Sun, 2017-12-03 at 23:18 +, Yuri Budilov wrote:
> Posted on Stack Overflow, sadly no replies, so trying here
>
>  CREATE TABLE X AS
> SELECT json_array_elements(json_rmq -> 'orders'::text) AS order
> FROM table_name
> WHERE blah;
> I get out of memory error.
>
> Is there anything I can do to unpack the above?
>
> The JSON column is about ~5 MB and it has about ~150,000 array
> row elements in 'orders' above.
>
> I tried work_mem values up to ~250MB and it did not help, the query
> takes about same time to fail.
>
> I guess this parameter does not help JSON processing.
>
> If there another parameter I can try? Something else?
>
> I don't have control of the size of the JSON payload, it arrives, we
> store it in a JSON column and then we need to crack it open.
>
> Many thanks!
>



Hello,

It would help if you advised:-
(a) version of PostgreSql being used.
(b) is column json_rmq defined as json or jsonb?
(c) OS.

Cheers,
Rob

Re: ERROR: could not load library libperl.so, PostgreSQL trying to CREATE EXTENSION plperlu

[Peter J. Holzer]

On 2017-11-30 15:49:28 -0800, Alan Hodgson wrote:
> On Thu, 2017-11-30 at 22:59 +, Ben Nachtrieb wrote:
> Thank you!  Solution: build them from source on the server?
> 
> Well, it would be more maintainable to find a source for packages built for
> your particular OS.

https://www.postgresql.org/download/linux/suse/?

> Or run a supported OS; that one looks pretty old.

SLES 12 is the current version of SLES.

hp

-- 
   _  | Peter J. Holzer| we build much bigger, better disasters now
|_|_) || because we have much more sophisticated
| |   | h...@hjp.at | management tools.
__/   | http://www.hjp.at/ | -- Ross Anderson 


signature.asc
Description: PGP signature

Re: JSON out of memory error on PostgreSQL 9.6.x

[John R Pierce]

On 12/3/2017 3:18 PM, Yuri Budilov wrote:


Posted on Stack Overflow, sadly no replies, so trying here

...


,,,

why did you email me personally ?


--
john r pierce, recycling bits in santa cruz

Re: JSON out of memory error on PostgreSQL 9.6.x

[Yuri Budilov]

the plot thickens!

I have more information.


The out-of-memory error happens if I also retrieve another JSON Column like so:


-- fails

CREATE TABLE csnbi_stg.junk4
AS
SELECT
   json_rmq->>'totalSize' as totalSize, -- this plus array below causes out 
of memory error
   json_array_elements(json_rmq -> 'orders'::text) AS orders
  FROM csnbi_stg.stg_rmq_prod_test_json_raw_jmcn stg_rmq_json_raw
 WHERE rmq_exchange_name = 'Staging.Salesforce.Order'
   AND rmq_message_id = 'd1200de2-30b0-4599-bb17-64405f45ca19';


if I *only* retrieve the JSON array by itself then it works:


CREATE TABLE csnbi_stg.junk5
AS
SELECT
  --  json_rmq->>'totalSize' as totalSize, -- take this OUT and below works
   json_array_elements(json_rmq -> 'orders'::text) AS orders
  FROM csnbi_stg.stg_rmq_prod_test_json_raw_jmcn stg_rmq_json_raw
 WHERE rmq_exchange_name = 'Staging.Salesforce.Order'
   AND rmq_message_id = 'd1200de2-30b0-4599-bb17-64405f45ca19';


THANK YOU




From: Yuri Budilov 
Sent: Monday, 4 December 2017 11:14 AM
To: rob stone; pgsql-general@lists.postgresql.org
Subject: Re: JSON out of memory error on PostgreSQL 9.6.x


hello good people


it is *not* JSONB, just plain JSON

the version 9.6.3 and running inside AWS RDS PostgreSQL (DBaaS)


the machine size is just 1 GB RAM and 1 CPU, is it a called "micro" size AWS 
RDS instance, we use it for DEV

we have also reproduced it on 2 CPU 8 GB RAM instance, FWIW.

It takes well under 1 min elapsed time to fail.


best regards and many thanks for trying to help me




From: rob stone 
Sent: Monday, 4 December 2017 11:01 AM
To: Yuri Budilov; John R Pierce; pgsql-general@lists.postgresql.org
Subject: Re: JSON out of memory error on PostgreSQL 9.6.x



On Sun, 2017-12-03 at 23:18 +, Yuri Budilov wrote:
> Posted on Stack Overflow, sadly no replies, so trying here
>
>  CREATE TABLE X AS
> SELECT json_array_elements(json_rmq -> 'orders'::text) AS order
> FROM table_name
> WHERE blah;
> I get out of memory error.
>
> Is there anything I can do to unpack the above?
>
> The JSON column is about ~5 MB and it has about ~150,000 array
> row elements in 'orders' above.
>
> I tried work_mem values up to ~250MB and it did not help, the query
> takes about same time to fail.
>
> I guess this parameter does not help JSON processing.
>
> If there another parameter I can try? Something else?
>
> I don't have control of the size of the JSON payload, it arrives, we
> store it in a JSON column and then we need to crack it open.
>
> Many thanks!
>



Hello,

It would help if you advised:-
(a) version of PostgreSql being used.
(b) is column json_rmq defined as json or jsonb?
(c) OS.

Cheers,
Rob

Re: JSON out of memory error on PostgreSQL 9.6.x

[John R Pierce]

On 12/3/2017 3:18 PM, Yuri Budilov wrote:
|CREATETABLEX ASSELECTjson_array_elements(json_rmq 
->'orders'::text)ASorderFROMtable_name WHEREblah;|


I get out of memory error.



are you sure thats a postgres error ?  are you doing this in psql, or 
what sort of application environment ?


how many rows does 'blah' match ?

what is...

SELECT pg_column_size(json_array_elements(json_rmq -> 'orders'::text))
  FROM table_name
  WHERE blah;

?


--
john r pierce, recycling bits in santa cruz

Re: JSON out of memory error on PostgreSQL 9.6.x

[Tom Lane]

Yuri Budilov  writes:
> The out-of-memory error happens if I also retrieve another JSON Column like 
> so:

> CREATE TABLE csnbi_stg.junk4
> AS
> SELECT
>json_rmq->>'totalSize' as totalSize, -- this plus array below causes 
> out of memory error
>json_array_elements(json_rmq -> 'orders'::text) AS orders
>   FROM csnbi_stg.stg_rmq_prod_test_json_raw_jmcn stg_rmq_json_raw

Ah.  The problem here is that "json_rmq->>'totalSize'" leaks some memory
on each execution, and it's executed again for each row produced by the
json_array_elements() SRF, and the memory can't be reclaimed until we've
finished the full output cycle for the SRF.  So the leakage (which is
more or less of the size of the JSON value, I think) accumulates across
150K executions in this example.

This is fixed as of v10.  It seems impractical to do anything about it
in previous release branches, although you could reformulate your query to
avoid it by not having any other expression evaluations occurring in the
same tlist as the SRF.  Something like this should work:

SELECT totalSize, json_array_elements(json_rmq -> 'orders'::text) AS orders
FROM
 (SELECT
   json_rmq->>'totalSize' as totalSize, json_rmq
  FROM csnbi_stg.stg_rmq_prod_test_json_raw_jmcn stg_rmq_json_raw
  WHERE ...
  OFFSET 0) ss;

regards, tom lane

Re: JSON out of memory error on PostgreSQL 9.6.x

[Yuri Budilov]

thank you, I will look into the work-around !






From: Tom Lane 
Sent: Monday, 4 December 2017 11:39 AM
To: Yuri Budilov
Cc: rob stone; pgsql-general@lists.postgresql.org
Subject: Re: JSON out of memory error on PostgreSQL 9.6.x

Yuri Budilov  writes:
> The out-of-memory error happens if I also retrieve another JSON Column like 
> so:

> CREATE TABLE csnbi_stg.junk4
> AS
> SELECT
>json_rmq->>'totalSize' as totalSize, -- this plus array below causes 
> out of memory error
>json_array_elements(json_rmq -> 'orders'::text) AS orders
>   FROM csnbi_stg.stg_rmq_prod_test_json_raw_jmcn stg_rmq_json_raw

Ah.  The problem here is that "json_rmq->>'totalSize'" leaks some memory
on each execution, and it's executed again for each row produced by the
json_array_elements() SRF, and the memory can't be reclaimed until we've
finished the full output cycle for the SRF.  So the leakage (which is
more or less of the size of the JSON value, I think) accumulates across
150K executions in this example.

This is fixed as of v10.  It seems impractical to do anything about it
in previous release branches, although you could reformulate your query to
avoid it by not having any other expression evaluations occurring in the
same tlist as the SRF.  Something like this should work:

SELECT totalSize, json_array_elements(json_rmq -> 'orders'::text) AS orders
FROM
 (SELECT
   json_rmq->>'totalSize' as totalSize, json_rmq
  FROM csnbi_stg.stg_rmq_prod_test_json_raw_jmcn stg_rmq_json_raw
  WHERE ...
  OFFSET 0) ss;

regards, tom lane

Re: JSON out of memory error on PostgreSQL 9.6.x

[Michael Paquier]

On Mon, Dec 4, 2017 at 9:39 AM, Tom Lane  wrote:
> Ah.  The problem here is that "json_rmq->>'totalSize'" leaks some memory
> on each execution, and it's executed again for each row produced by the
> json_array_elements() SRF, and the memory can't be reclaimed until we've
> finished the full output cycle for the SRF.  So the leakage (which is
> more or less of the size of the JSON value, I think) accumulates across
> 150K executions in this example.
>
> This is fixed as of v10.  It seems impractical to do anything about it
> in previous release branches, although you could reformulate your query to
> avoid it by not having any other expression evaluations occurring in the
> same tlist as the SRF.  Something like this should work:

Yeah, I agree with that. One similar leak has actually been fixed with
this commit, and the infrastructure of v10 has made this fix dead
simple:
commit: 0c25e9652461c08b5caef259a6af27a38707e07a
author: Tom Lane 
date: Fri, 6 Oct 2017 14:28:42 -0400
Fix intra-query memory leakage in nodeProjectSet.c.
https://www.postgresql.org/message-id/20171005230321.28561.15...@wrigleys.postgresql.org

nodeProjectSet.c really makes tuple-level memory handling way easier
based on my studies of this code.
-- 
Michael

ISO8601 vs POSIX offset clarification

[Bharanee Rathna]

Hi,

the documentation around how numeric offsets are parsed from strings is a
bit confusing, are they supposed to be treated as ISO8601 or POSIX ?


e.g.


select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time zone
'+11:00';
 timezone

-

 2017-11-30 13:00:00


 select '2017-12-01 11:00:00 -11:00'::timestamp with time zone at time zone
'+11:00';

  timezone

-

 2017-12-01 11:00:00



The Table 8-12. Time Zone Input section at
https://www.postgresql.org/docs/9.3/static/datatype-datetime.html seems to
imply that numeric offsets would be treated as ISO8601. It's all a big
confusing and would appreciate some clarification or pointer to
documentation.


Thanks

Re: ISO8601 vs POSIX offset clarification

[Tom Lane]

Bharanee Rathna  writes:
> the documentation around how numeric offsets are parsed from strings is a
> bit confusing, are they supposed to be treated as ISO8601 or POSIX ?

Our documentation about this says clearly that Postgres considers offsets
to be ISO (positive-east-of-Greenwich) everywhere except in POSIX-style
time zone names.

> The Table 8-12. Time Zone Input section at
> https://www.postgresql.org/docs/9.3/static/datatype-datetime.html seems to
> imply that numeric offsets would be treated as ISO8601.

How do you read an entry such as

-8:00   |   ISO-8601 offset for PST

as being in any way vague about which convention the "-8" is read in?

regards, tom lane

Re: ISO8601 vs POSIX offset clarification

[Bharanee Rathna]

Sorry I didn't mean for it to come out as a complaint, just that I am
confused since the result of the SQL query was not what I expected. I
expected +11:00 to be 11 hours east of UTC which wasn't the case.



On 4 December 2017 at 13:55, Tom Lane  wrote:

> Bharanee Rathna  writes:
> > the documentation around how numeric offsets are parsed from strings is a
> > bit confusing, are they supposed to be treated as ISO8601 or POSIX ?
>
> Our documentation about this says clearly that Postgres considers offsets
> to be ISO (positive-east-of-Greenwich) everywhere except in POSIX-style
> time zone names.
>
> > The Table 8-12. Time Zone Input section at
> > https://www.postgresql.org/docs/9.3/static/datatype-datetime.html seems
> to
> > imply that numeric offsets would be treated as ISO8601.
>
> How do you read an entry such as
>
> -8:00   |   ISO-8601 offset for PST
>
> as being in any way vague about which convention the "-8" is read in?
>
> regards, tom lane
>

Re: ISO8601 vs POSIX offset clarification

[Bharanee Rathna]

To be more specific, I expected the output of both these queries to be the
same.

# select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time
zone '+11:00';

  timezone

-

 2017-11-30 13:00:00


# select '2017-12-01 11:00:00 +11:00'::timestamp with time zone at time
zone 'Australia/Melbourne';

  timezone

-

 2017-12-01 11:00:00


Cheers

On 4 December 2017 at 13:59, Bharanee Rathna  wrote:

> Sorry I didn't mean for it to come out as a complaint, just that I am
> confused since the result of the SQL query was not what I expected. I
> expected +11:00 to be 11 hours east of UTC which wasn't the case.
>
>
>
> On 4 December 2017 at 13:55, Tom Lane  wrote:
>
>> Bharanee Rathna  writes:
>> > the documentation around how numeric offsets are parsed from strings is
>> a
>> > bit confusing, are they supposed to be treated as ISO8601 or POSIX ?
>>
>> Our documentation about this says clearly that Postgres considers offsets
>> to be ISO (positive-east-of-Greenwich) everywhere except in POSIX-style
>> time zone names.
>>
>> > The Table 8-12. Time Zone Input section at
>> > https://www.postgresql.org/docs/9.3/static/datatype-datetime.html
>> seems to
>> > imply that numeric offsets would be treated as ISO8601.
>>
>> How do you read an entry such as
>>
>> -8:00   |   ISO-8601 offset for PST
>>
>> as being in any way vague about which convention the "-8" is read in?
>>
>> regards, tom lane
>>
>
>