Re: [HACKERS] Proposal: Trigonometric functions in degrees

2015-10-25 Thread Emre Hasegeli
> Currently PostgreSQL only has trigonometric functions that work in
> radians. I think it would be quite useful to have an equivalent set of
> functions that worked in degrees. In other environments these are
> commonly spelled sind(), cosd(), etc.

I would prefer gradian over degree.


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


Re: [HACKERS] Proposal: Trigonometric functions in degrees

2015-10-25 Thread Michael Paquier
On Sun, Oct 25, 2015 at 6:16 PM, Emre Hasegeli  wrote:
>> Currently PostgreSQL only has trigonometric functions that work in
>> radians. I think it would be quite useful to have an equivalent set of
>> functions that worked in degrees. In other environments these are
>> commonly spelled sind(), cosd(), etc.
>
> I would prefer gradian over degree.

This may be a matter of personal taste, but I am personally used to
degrees since primary school. By the way, +1 for the proposal.
-- 
Michael


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


Re: [HACKERS] pgbench gaussian/exponential docs improvements

2015-10-25 Thread Tomas Vondra



On 10/25/2015 10:01 PM, Fabien COELHO wrote:



[...]

So either the information is important and then should be placed in
the docs directly, or it's not and then linking to wikipedia is
pointless because the users are not interested in learning all the
details about each distribution function.


What is important is that these distributions can be used from pgbench.
What is a gaussian or an exponential distribution is *not* important as
such.

For me it is not the point of pg documentation to explain probability
theory, but just to provide *precise* information about what is actually
available, for someone who would be interested, without having to read
the source code. At least that is the idea behind the current
documentation.


OK, fair enough. OTOH many of our users don't have immediate knowledge 
of statistical distributions, so if we could give them additional info 
in a reasonable way, that'd be good.





Firstly, it'd be nice if we could add some figures illustrating the
distributions - much better than explaining the shapes in text. I
don't know if we include figures in the existing docs (probably not),
but generating the figures is rather simple.


There is basically no figures in the documentation. Too bad, but it is
understandable: what should be the format (svg, jpg, png, ...), should
it be generated (gnuplot, others), what is the impact on the
documentation build (html, epub, pdf, ...), how portable should it be,
what about compressed formats vs git diffs?

Once you start asking these questions you understand why there are no
figures:-)


I don't see why diffs would be a problem.


I was not only thinking of mathematical figures, I was also thinking of
graphics, some format may be zip containing XML stuff for instance.


But we don't need it here, so why should we care about it too much?




In other words, the general shape of the curve will be exactly the
same no matter the actual min/max (except that for longer intervals
the values will be lower, as there are more possible values).

I don't really see how it's related to this?

   [(max-min)/2 - thresholds, (max-min)/2 + threshold]


The gaussian distribution is about reals, but it is used for integers,
so there is a projection on integers from the real values. The function
should compute the probability of drawing a given integer "i" in the
interval, that is given min, max and threshold, what is the probability
of drawing i.


I do understand that. I'm trying to explain that "threshold" is in fact 
completely disconnected from min and max, as the transformation scales 
the data to [-1,1] like this


2.0 * (i - min - mu + 0.5) / (max - min + 1)

and only then the 'threshold' coefficient is applied. And if I read the 
Box-Muller transformation correctly, it generates data with standard 
Normal distribution from [-threshold, threshold] and then transforms 
them to the right mean etc.


But maybe that's what the first sentence is trying to say? I mean this:

For a Gaussian distribution, the interval is mapped onto a standard
normal distribution (the classical bell-shaped Gaussian curve)
truncated at -threshold on the left and +threshold on the right.

I'm asking about this because it wasn't to me immediately clear whether 
I need to tweak this for data sets with different scales, but apparently 
not. After reading the docs again I think that's also clear from last 
sentence that relates threshold and 67% and 95%.


Anyway, the references to "standard normal distribution" are a bit 
sloppy - "standard" usually means normal distribution with exactly mu=0 
and sigma=1. So it's a bit strange to say


standard normal distribution, with mean mu defined as (max+min)/2.0

because that's not a standard normal distribution at all. I propose to 
fix this by removing the "standard".


[1] as wikipedia notes, Gauss himself used different sigma




Could we simplify the equation a bit? It's needlessly difficult to
realize it's actually just CDF(i+0.5) - CDF(i-0.5). I think it'd be
good to first define the CDF and then just use that.


ISTM that PHI is *the* normal CDF, which is more or less available as
such in various environment (matlab, python, excel...). Well, why not
defined the particular CDF and use it. Not sure the text would be that
much lighter, though.


PHI is the CDF of the normal distribution, not the modified
probability distribution here (with threshold and scaled to the
desired interval).


Yep, that is exactly what I was saying, I think.


I think we're talking about slightly different things. Essentially the 
transformation transforms Normal distribution (with PHI as CDF) into 
another statistical distribution (with the thresholds and such), and a 
different CDF, let's say CDF2, which is


  CDF2(x) = PHI(2.0 * threshold * ...) / (2.0 * PHI(threshold) - 1.0)

and then the probability of "i" is

  P(X=i) = CDF2(i+0.5) - CDF2(i-0.5)

Which is what I meant by simplifying the equation. Not that it'd make 
easier to 

Re: [HACKERS] remaining open items

2015-10-25 Thread Ian Barwick
On 17/10/15 04:31, Alvaro Herrera wrote:
> Robert Haas wrote:
>>> The other item on me is the documentation patch by Emre Hasegeli for
>>> usage of the inclusion opclass framework in BRIN.  I think it needs some
>>> slight revision by some native English speaker and I'm not completely in
>>> love with the proposed third column in the table it adds, but otherwise
>>> is factually correct as far as I can tell.
>>
>> I'm not clear whether you are asking for help with this, or ...?
> 
> I enlisted the help of Ian Barwick for this one.

Revised version of Emre's patch attached, apologies for the delay.


Regards

Ian Barwick



-- 
 Ian Barwick   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
diff --git a/doc/src/sgml/brin.sgml b/doc/src/sgml/brin.sgml
new file mode 100644
index dc5405e..673486f
*** a/doc/src/sgml/brin.sgml
--- b/doc/src/sgml/brin.sgml
*** typedef struct BrinOpcInfo
*** 535,541 
defined by the user for other data types using equivalent definitions,
without having to write any source code; appropriate catalog entries being
declared is enough.  Note that assumptions about the semantics of operator
!   strategies are embedded in the support procedures's source code.
   
  
   
--- 535,541 
defined by the user for other data types using equivalent definitions,
without having to write any source code; appropriate catalog entries being
declared is enough.  Note that assumptions about the semantics of operator
!   strategies are embedded in the support procedure's source code.
   
  
   
*** typedef struct BrinOpcInfo
*** 566,584 
 
  
   Support Procedure 1
!  function brin_minmax_opcinfo()
  
  
   Support Procedure 2
!  function brin_minmax_add_value()
  
  
   Support Procedure 3
!  function brin_minmax_consistent()
  
  
   Support Procedure 4
!  function brin_minmax_union()
  
  
   Operator Strategy 1
--- 566,584 
 
  
   Support Procedure 1
!  internal function brin_minmax_opcinfo()
  
  
   Support Procedure 2
!  internal function brin_minmax_add_value()
  
  
   Support Procedure 3
!  internal function brin_minmax_consistent()
  
  
   Support Procedure 4
!  internal function brin_minmax_union()
  
  
   Operator Strategy 1
*** typedef struct BrinOpcInfo
*** 603,607 
--- 603,793 
 

   
+ 
+  
+   To write an operator class for a complex datatype which has values
+   included within another type, it's possible to use the inclusion support
+   procedures alongside the corresponding operators, as shown
+   in .  It requires
+   only a single additional function, which can be written in any language.
+   More functions can be defined for additional functionality.  All operators
+   are optional.  Some operators require other operators, as shown as
+   dependencies on the table.
+  
+ 
+  
+   Procedure and Support Numbers for Inclusion Operator Classes
+   
+
+ 
+  Operator class member
+  Object
+  Dependency
+ 
+
+
+ 
+  Support Procedure 1
+  internal function brin_inclusion_opcinfo()
+  
+ 
+ 
+  Support Procedure 2
+  internal function brin_inclusion_add_value()
+  
+ 
+ 
+  Support Procedure 3
+  internal function brin_inclusion_consistent()
+  
+ 
+ 
+  Support Procedure 4
+  internal function brin_inclusion_union()
+  
+ 
+ 
+  Support Procedure 11
+  function to merge two elements
+  
+ 
+ 
+  Support Procedure 12
+  optional function to check whether two elements are mergeable
+  
+ 
+ 
+  Support Procedure 13
+  optional function to check if an element is contained within another
+  
+ 
+ 
+  Support Procedure 14
+  optional function to check whether an element is empty
+  
+ 
+ 
+  Operator Strategy 1
+  operator left-of
+  Operator Strategy 4
+ 
+ 
+  Operator Strategy 2
+  operator does-not-extend-to-the-right-of
+  Operator Strategy 5
+ 
+ 
+  Operator Strategy 3
+  operator overlaps
+  
+ 
+ 
+  Operator Strategy 4
+  operator right-of
+  Operator Strategy 2
+ 
+ 
+  Operator Strategy 5
+  operator does-not-extend-to-the-right-of
+  Operator Strategy 1
+ 
+ 
+  Operator Strategy 6, 18
+  operator same-as-or-equal-to
+  Operator Strategy 7
+ 
+ 
+  Operator Strategy 7, 13, 16, 24, 25
+  operator contains-or-equal-to
+  
+ 
+ 
+  Operator Strategy 8, 14, 26, 27
+  operator is-contained-by-or-equal-to
+  Operator Strategy 3
+ 
+ 
+  Operator Strategy 9
+  operator does-not-extend-above
+  Operator Strategy 11
+ 
+ 
+  

Re: [HACKERS] questions about PG update performance

2015-10-25 Thread Kisung Kim
2015-10-26 11:12 GMT+09:00 Michael Paquier :

>
>
> On Mon, Oct 26, 2015 at 10:52 AM, Kisung Kim  wrote:
>
>> Because of the internal implementation of MVCC in PG
>> the update of a row is actually a insertion of a new version row.
>> So if the size of a row is huge, then it incurs some overhead compare to
>> in-place update strategy.
>>
>
> Yeah, that's how an UPDATE in Postgres for MVCC usage. The xmax of the old
> row is updated, and a new row is inserted with an xmin equal to the
> previous xmax. So if you update tuple fields one by one the cost is going
> to be high.
>
>
>> Let's assume that a table has 200 columns,
>> and a user updates one of the columns of one row in the table.
>> Then PG will rewrite the whole contents of the updated row
>> including the updated columns and not-updated columns.
>>
>
> When a table has a large number of columns, usually I would say that you
> have a normalization problem and such schemas could be split into a smaller
> set of tables, minimizing the UPDATE cost.
>
>
>> I'm not sure about the implementation of Oracle's update.
>> But if the Oracle can overwrite only the updated column,
>> the performance difference between Oracle and PG in that case may be
>> significant.
>>
>> I researched about this issues in mailing list and google.
>> But I've not found anything related to this issues.
>>
>
> What you are looking at here is columnar storage, Alvaro and 2nd Quadrant
> folks have been doing some work in this area recently:
> http://www.postgresql.org/message-id/20150831225328.GM2912@alvherre.pgsql
> Also, you may want to have a look at cstore_fdw:
> https://github.com/citusdata/cstore_fdw.
> Regards,
> --
> Michael
>

Thank you for your reply.
I already know about the column store and that it is optimized for the case
I described.

However, what I want to know is about the update performance difference
between PG and Oracle if there any.
The case I described is for exaggerating the difference between PG and
Oracle.

I want to explain for our clients that PG's update performance is
comparable to Oracle's.

Regards,


Re: [HACKERS] questions about PG update performance

2015-10-25 Thread Любен Каравелов

- Цитат от Kisung Kim (ks...@bitnine.co.kr), на 26.10.2015 в 04:36 -

> However, what I want to know is about the update performance difference
> between PG and Oracle if there any.
> The case I described is for exaggerating the difference between PG and
> Oracle.
> 
> I want to explain for our clients that PG's update performance is
> comparable to Oracle's.
> 

Oracle is also using MVCC but copies the old row in the rollback segment and
rewrites the values in-place. It is still 2 writes as in Postgres. The
difference is on roll-back and cleaning the row when it is not needed
anymore.

Regards,

--
Luben Karavelov



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


Re: [HACKERS] questions about PG update performance

2015-10-25 Thread Amit Kapila
On Mon, Oct 26, 2015 at 9:03 AM, Любен Каравелов  wrote:
>
>
> - Цитат от Kisung Kim (ks...@bitnine.co.kr), на 26.10.2015 в 04:36
-
>
> > However, what I want to know is about the update performance difference
> > between PG and Oracle if there any.
> > The case I described is for exaggerating the difference between PG and
> > Oracle.
> >
> > I want to explain for our clients that PG's update performance is
> > comparable to Oracle's.
> >
>
> Oracle is also using MVCC but copies the old row in the rollback segment
and
> rewrites the values in-place.

I think Oracle just copies the changed part of old row to rollback segment.
Also in Redo logs, it just writes the changed column value (both old and
new).  So for the case we are discussing in this thread (one changed
column out of 200 columns), Oracle will just write the old value of that
column in Redo and then in rollback segment, and write the new value
in Redo and then do the in-place update in heap row.

In PostgreSQL, the whole new row is written in heap and diff tuple
(difference
of old and new tuple; this optimization is done in 9.4, commit a3115f0d)
in WAL.  I think we can try to optimize and use the same technique for
heap as used for WAL to make PostgreSQL more efficient for such scenario's,
however as of today, my guess is that PostgreSQL's update would be lagging
in this area.

> It is still 2 writes as in Postgres.

The difference is in the amount of data written per write.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


[HACKERS] questions about PG update performance

2015-10-25 Thread Kisung Kim
Dear,

I have a question about update performance of PG.

Because of the internal implementation of MVCC in PG
the update of a row is actually a insertion of a new version row.
So if the size of a row is huge, then it incurs some overhead compare to
in-place update strategy.

Let's assume that a table has 200 columns,
and a user updates one of the columns of one row in the table.
Then PG will rewrite the whole contents of the updated row
including the updated columns and not-updated columns.

I'm not sure about the implementation of Oracle's update.
But if the Oracle can overwrite only the updated column,
the performance difference between Oracle and PG in that case may be
significant.

I researched about this issues in mailing list and google.
But I've not found anything related to this issues.

Thank you.

Kisung Kim.






(C)Bitnine, Kisung Kim, Ph.D
https://sites.google.com/site/kisungresearch/
E-mail : ks...@bitnine.co.kr
Office phone : 070-4800-3321
Mobile phone : 010-7136-0834
Fax : 02-568-1332


Re: [HACKERS] ATT_FOREIGN_TABLE and ATWrongRelkindError()

2015-10-25 Thread Amit Langote
On 2015/10/23 19:02, Amit Langote wrote:
> On 2015/10/23 18:51, Etsuro Fujita wrote:
>>
>> This is really really nitpicking, but I noticed that there is an implicit
>> rule concerning the message format in ATWrongRelkindError; if more than
>> two objects are present, the message is "\"%s\" is not a foo, bar, or
>> baz". ("or" is preceded by a comma!)  So, would it be better that the
>> former is "\"%s\" is not a table, view, or foreign table"?
> 
> Oops! Yeah, I missed the comma there. That seems like a generally
> preferred punctuation rule (the comma before conjunction(s) I mean).

Here is a patch rectifying that mistake.

Thanks,
Amit
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index a5bc508..58a7313 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -4337,7 +4337,7 @@ ATWrongRelkindError(Relation rel, int allowed_targets)
 			msg = _("\"%s\" is not a table or view");
 			break;
 		case ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE:
-			msg = _("\"%s\" is not a table, view or foreign table");
+			msg = _("\"%s\" is not a table, view, or foreign table");
 			break;
 		case ATT_TABLE | ATT_VIEW | ATT_MATVIEW | ATT_INDEX:
 			msg = _("\"%s\" is not a table, view, materialized view, or index");

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


Re: [HACKERS] Re: [BUGS] BUG #13611: test_postmaster_connection failed (Windows, listen_addresses = '0.0.0.0' or '::')

2015-10-25 Thread Tatsuo Ishii
> As I stated upthread, PQping("host='0.0.0.0'") is _not portable_.  It works on
> GNU/Linux, which I used for that demo.  It fails on OpenBSD and Windows.
> 
>> I'd be inclined to suggest fixing it like this:
>> 
>> /* If postmaster is listening on "*", use localhost 
>> */
>> -   if (strcmp(host_str, "*") == 0)
>> +   if (strcmp(host_str, "*") == 0 ||
>> +   strcmp(host_str, "0.0.0.0") == 0 ||
>> +   strcmp(host_str, "::") == 0)
>> strcpy(host_str, "localhost");
>> 
>> which covers the cases we document as supported.
> 
> On RHEL 5 and some other "active adult" systems, "localhost" does not reach a
> listen_addresses='::' server.  IPv6 is available, but "localhost" resolves to
> 127.0.0.1 only.
> 
> The latest systems resolve "localhost" to both 127.0.0.1 and ::1, in which
> case PQping("host='localhost'") will attempt both addresses in an unspecified
> order.  Given a postmaster with listen_addresses='0.0.0.0', contacting ::1
> first will fail (fine) or reach a different postmaster (not fine).
> 
> Kondo's design is correct.

So more proper fix looks like this?
diff --git a/src/bin/pg_ctl/pg_ctl.c b/src/bin/pg_ctl/pg_ctl.c
index dacdfef..23d5a3c 100644
--- a/src/bin/pg_ctl/pg_ctl.c
+++ b/src/bin/pg_ctl/pg_ctl.c
@@ -646,9 +646,11 @@ test_postmaster_connection(pgpid_t pm_pid, bool 
do_checkpoint)
return 
PQPING_NO_ATTEMPT;
}
 
-   /* If postmaster is listening 
on "*", use localhost */
-   if (strcmp(host_str, "*") == 0)
-   strcpy(host_str, 
"localhost");
+   /* If postmaster is listening 
on "*", "0.0.0.0" or "::", use 127.0.0.1 */
+   if (strcmp(host_str, "*") == 0 
||
+   strcmp(host_str, 
"0.0.0.0") == 0 ||
+   strcmp(host_str, "::") 
== 0)
+   strcpy(host_str, 
"127.0.0.1");
 
/*
 * We need to set 
connect_timeout otherwise on Windows

Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp


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


[HACKERS] About BoringSSL, an OpenSSL fork

2015-10-25 Thread Michael Paquier
Hi all,

Perhaps some of you guys knew about that, but I just found about this
stuff this morning:
https://boringssl.googlesource.com/boringssl/+/HEAD/PORTING.md
Looking at the porting section many routines have changed compared to
OpenSSL. I can't imagine this fork to become a complete replacement of
OpenSSL, but it may be worth considering an integration in Postgres
code depending on the features it will have (Curve25519,
Ed25519 mentioned). Also since 9.4 the SSL code paths have been
rearranged to allow more implementations to be done with other SSL
libraries.

Note that I am wondering also about the long-term stability and
potential backward-incompatibilities of the routines in this fork
though (recall for example v8 major breakage wround 3.14, if I recall
correctly this version number).
Thoughts?
-- 
Michael


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


Re: [HACKERS] [DESIGN] ParallelAppend

2015-10-25 Thread Kouhei Kaigai
> On Sat, Jul 25, 2015 at 11:13 PM, Kouhei Kaigai  wrote:
> > I'm recently working/investigating on ParallelAppend feature
> > towards the next commit fest. Below is my design proposal.
> >
> > 1. Concept
> > --
> > Its concept is quite simple anybody might consider more than once.
> > ParallelAppend node kicks background worker process to execute
> > child nodes in parallel / asynchronous.
> > It intends to improve the performance to scan a large partitioned
> > tables from standpoint of entire throughput, however, latency of
> > the first multi-hundred rows are not scope of this project.
> > From standpoint of technology trend, it primarily tries to utilize
> > multi-cores capability within a system, but also enables to expand
> > distributed database environment using foreign-tables inheritance
> > features.
> > Its behavior is very similar to Funnel node except for several
> > points, thus, we can reuse its infrastructure we have had long-
> > standing discussion through the v9.5 development cycle.
> 
> Now that I've got more of the parallel infrastructure committed, I've
> been starting to have a little time to think about what we might want
> to do after we get PartialSeqScan committed.  I'm positive on doing
> something with the Append node and parallelism, but I'm negative on
> doing what you've described here.
> 
> I don't think the Append node has any business launching workers.
> That's the job of Gather.  Append may need to do something
> parallel-aware, but starting workers is not that thing.  Without
> making any changes at all to Append, we can use it like this:
> 
> Gather
> -> Append
>   -> Partial Seq Scan on p1
>   -> Partial Seq Scan on p2
>   -> Partial Seq Scan on p3
> 
> The Gather node will spin up workers and in each worker we will ask
> the Append nodes for tuples.  Append will ask the first
> not-yet-completed child for tuples, so the workers will cooperatively
> scan first p1, then p2, then p3.  This is great: instead of merely
> doing a parallel seq scan of a single table, we can do a parallel seq
> scan of a partitioned table.  However, there are two improvements we
> can make.  First, we can teach Append that, when running in parallel,
> it should initialize a chunk of dynamic shared memory with an array
> indicating how many workers are currently working on each subplan.
> Each new worker should join a subplan with the minimum number of
> workers, work on that one until it's completely, and then pick a new
> subplan.  This minimizes contention.  Second, we can teach the Append
> to serve as a parent not only for intrinsically parallel nodes like
> Partial Seq Scan, but also for other nodes, say, an Index Scan.  When
> an Append is running in parallel but with non-parallel-aware children,
> each such child can be claimed by at most one worker process and will
> be run to completion by that worker process.  For example:
> 
> Gather
> -> Append
>   -> Index Scan on p1
>   -> Partial Seq Scan on p2
>   -> Index Scan on p3
> 
> The first worker which executes the Append should begin the index scan
> on p1 and the second should begin the index scan on p3.  The remaining
> workers, and those two once they're finished, can work on p2.
> 
> Proceeding in this way, I don't think we need a separate Parallel
> Append node.  Rather, we can just give the existing Append node some
> extra smarts that are used only when it's running in parallel.
>
I entirely agree with your suggestion.

We may be able to use an analogy between PartialSeqScan and the
parallel- aware Append node.
PartialSeqScan fetches blocks pointed by the index on shared memory
segment, thus multiple workers eventually co-operate to scan a table
using round-robin manner even though individual worker fetches comb-
shaped blocks.
If we assume individual blocks are individual sub-plans on the parallel
aware Append, it performs very similar. A certain number of workers
(more than zero) is launched by Gather node, then the parallel aware
Append node fetches one of the sub-plans if any.

I think, this design also gives additional flexibility according to
the required parallelism by the underlying sub-plans.
Please assume the "PartialSeqScan on p2" in the above example wants
3 workers to process the scan, we can expand the virtual array of
the sub-plans as follows. Then, if Gather node kicks 5 workers,
individual workers are assigned on some of plans. If Gather node
could kick less than 5 workers, the first exit worker picks the
second sub-plan, then it eventually provides the best parallelism.

++
|sub-plan |   * Sub-Plan 1 ... Index Scan on p1
|index on *-> * Sub-Plan 2 ... PartialSeqScan on p2
|shared   |   * Sub-Plan 2 ... PartialSeqScan on p2
|memory   |   * Sub-Plan 2 ... PartialSeqScan on p2
+-+   * Sub-Plan 3 ... Index Scan on p3

Here is no matter even if Append node has multiple parallel-aware
sub-plans. When "PartialSeqScan on p4" is added, all we need 

Re: [HACKERS] questions about PG update performance

2015-10-25 Thread Michael Paquier
On Mon, Oct 26, 2015 at 10:52 AM, Kisung Kim  wrote:

> Because of the internal implementation of MVCC in PG
> the update of a row is actually a insertion of a new version row.
> So if the size of a row is huge, then it incurs some overhead compare to
> in-place update strategy.
>

Yeah, that's how an UPDATE in Postgres for MVCC usage. The xmax of the old
row is updated, and a new row is inserted with an xmin equal to the
previous xmax. So if you update tuple fields one by one the cost is going
to be high.


> Let's assume that a table has 200 columns,
> and a user updates one of the columns of one row in the table.
> Then PG will rewrite the whole contents of the updated row
> including the updated columns and not-updated columns.
>

When a table has a large number of columns, usually I would say that you
have a normalization problem and such schemas could be split into a smaller
set of tables, minimizing the UPDATE cost.


> I'm not sure about the implementation of Oracle's update.
> But if the Oracle can overwrite only the updated column,
> the performance difference between Oracle and PG in that case may be
> significant.
>
> I researched about this issues in mailing list and google.
> But I've not found anything related to this issues.
>

What you are looking at here is columnar storage, Alvaro and 2nd Quadrant
folks have been doing some work in this area recently:
http://www.postgresql.org/message-id/20150831225328.GM2912@alvherre.pgsql
Also, you may want to have a look at cstore_fdw:
https://github.com/citusdata/cstore_fdw.
Regards,
-- 
Michael


Re: [HACKERS] PATCH: 9.5 replication origins fix for logical decoding

2015-10-25 Thread Craig Ringer
On 19 October 2015 at 21:43, Craig Ringer  wrote:
> Hi all
>
> Patch revision 3 attached. It's a one-liner, with just the fix, and an
> explanatory note in the patch header.

I'm bumping this because I think it's important not to miss it for
9.5, so it can't wait for the commitfest.

It's just the one-liner with the fix its self.


-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


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


[HACKERS] pgbench gaussian/exponential docs improvements

2015-10-25 Thread Tomas Vondra

Hi,

I've been looking at the checkpoint patches (sorting, flush and FPW 
compensation) and realized we got gaussian/exponential distributions in 
pgbench which is useful for simulating simple non-uniform workloads.


But I think the current docs is a bit too difficult to understand for 
people without deep insight into statistics and shapes of probability 
distributions.


Firstly, it'd be nice if we could add some figures illustrating the 
distributions - much better than explaining the shapes in text. I don't 
know if we include figures in the existing docs (probably not), but 
generating the figures is rather simple.


A few more comments:


By default, or when uniform is specified, all values in the range are
drawn with equal probability. Specifying gaussian or exponential
options modifies this behavior; each requires a mandatory threshold
which determines the precise shape of the distribution.


I find the 'threshold' name to be rather unfortunate, as none of the 
probability distribution functions that I know use this term. And even 
if there's one probability function that uses 'threshold' it has very 
little meaning in the others. For example the exponential distribution 
uses 'rate' (lambda). I'd prefer a neutral name (e.g. 'parameter').



For a Gaussian distribution, the interval is mapped onto a standard
normal distribution (the classical bell-shaped Gaussian curve)
truncated at -threshold on the left and +threshold on the right.


Probably nitpicking, but left/right of what? I assume the normal 
distribution is placed at 0, so it's left/right of zero.



To be precise, if PHI(x) is the cumulative distribution function of
the standard normal distribution, with mean mu defined as (max + min)
/ 2.0, then value i between min and max inclusive is drawn with
probability: (PHI(2.0 * threshold * (i - min - mu + 0.5) / (max -
min + 1)) - PHI(2.0 * threshold * (i - min - mu - 0.5) / (max - min +
1))) / (2.0 * PHI(threshold) - 1.0). Intuitively, the larger the
threshold, the more frequently values close to the middle of the
interval are drawn, and the less frequently values close to the min
and max bounds.


Could we simplify the equation a bit? It's needlessly difficult to 
realize it's actually just CDF(i+0.5) - CDF(i-0.5). I think it'd be good 
to first define the CDF and then just use that.



About 67% of values are drawn from the middle 1.0 / threshold and 95%
in the middle 2.0 / threshold; for instance, if threshold is 4.0, 67%
of values are drawn from the middle quarter and 95% from the middle
half of the interval.


This seems broken - too many sentences about the 67% and 95%.


The minimum threshold is 2.0 for performance of the Box-Muller
transform.


Does it make sense to explicitly mention the implementation detail 
(Box-Muller transform) here?



regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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


Re: [HACKERS] pgbench gaussian/exponential docs improvements

2015-10-25 Thread Tomas Vondra



On 10/25/2015 08:11 PM, Fabien COELHO wrote:


Hello Tomas,


I've been looking at the checkpoint patches (sorting, flush and FPW
compensation) and realized we got gaussian/exponential distributions
in pgbench which is useful for simulating simple non-uniform workloads.


Indeed.


But I think the current docs is a bit too difficult to understand for
people without deep insight into statistics and shapes of probability
distributions.


I think the idea is that (1) if you do not know anything distributions,
probably you do not want expo/gauss, and (2) pg documentation should not
try to be an introductory course in probability theory.

AFAICR I suggested to point to relevant wikipedia pages but this has
been more or less rejected, so it ended up as it is, which is indeed
pretty unconvincing.


I don't think links to wikipedia are all that useful in this context.

Firstly, we have no control over wikipedia pages so we can't point the 
users to particular sections of the page (as we don't know if it gets 
rewritten tomorrow).


So either the information is important and then should be placed in the 
docs directly, or it's not and then linking to wikipedia is pointless 
because the users are not interested in learning all the details about 
each distribution function.



Firstly, it'd be nice if we could add some figures illustrating the
distributions - much better than explaining the shapes in text. I
don't know if we include figures in the existing docs (probably not),
but generating the figures is rather simple.


There is basically no figures in the documentation. Too bad, but it is
understandable: what should be the format (svg, jpg, png, ...), should
it be generated (gnuplot, others), what is the impact on the
documentation build (html, epub, pdf, ...), how portable should it be,
what about compressed formats vs git diffs?

Once you start asking these questions you understand why there are no
figures:-)


I don't see why diffs would be a problem. Include gnuplot source files, 
then build the appropriate format for each output format (eps for pdf, 
png for web, ...).


But yes, it definitely requires some work on the Makefiles.


For a Gaussian distribution, the interval is mapped onto a standard
normal distribution (the classical bell-shaped Gaussian curve)
truncated at -threshold on the left and +threshold on the right.


Probably nitpicking, but left/right of what? I assume the normal
distribution is placed at 0, so it's left/right of zero.


No, it is around the middle of the interval.


You mean [min,max] interval? I believe the transformation

2.0 * threshold * (i - min - mu + 0.5) / (max - min + 1)

essentially moves the mean into 0, scales the data to [0,1] and then 
applies the threshold.


In other words, the general shape of the curve will be exactly the same 
no matter the actual min/max (except that for longer intervals the 
values will be lower, as there are more possible values).


I don't really see how it's related to this?

[(max-min)/2 - thresholds, (max-min)/2 + threshold]




To be precise, if PHI(x) is the cumulative distribution function of
the standard normal distribution, with mean mu defined as (max + min)
/ 2.0, then value i between min and max inclusive is drawn with
probability: (PHI(2.0 * threshold * (i - min - mu + 0.5) / (max -
min + 1)) - PHI(2.0 * threshold * (i - min - mu - 0.5) / (max - min +
1))) / (2.0 * PHI(threshold) - 1.0). Intuitively, the larger the
threshold, the more frequently values close to the middle of the
interval are drawn, and the less frequently values close to the min
and max bounds.


Could we simplify the equation a bit? It's needlessly difficult to
realize it's actually just CDF(i+0.5) - CDF(i-0.5). I think it'd be
good to first define the CDF and then just use that.


ISTM that PHI is *the* normal CDF, which is more or less available as
such in various environment (matlab, python, excel...). Well, why not
defined the particular CDF and use it. Not sure the text would be that
much lighter, though.


PHI is the CDF of the normal distribution, not the modified probability 
distribution here (with threshold and scaled to the desired interval).



About 67% of values are drawn from the middle 1.0 / threshold and 95%
in the middle 2.0 / threshold; for instance, if threshold is 4.0, 67%
of values are drawn from the middle quarter and 95% from the middle
half of the interval.


This seems broken - too many sentences about the 67% and 95%.


The point is to provide rules of thumb to describe how the distribution
is shaped. Any better sentence is welcome.


Ah, I misread the sentence initially. I haven't realized it speaks about 
1/threshold in the first part, and the second part is an example for 
threshold=4.0. So I thought it's a repetition of the first part.





The minimum threshold is 2.0 for performance of the Box-Muller
transform.


Does it make sense to explicitly mention the implementation detail
(Box-Muller transform) here?


It is 

Re: [HACKERS] pgbench gaussian/exponential docs improvements

2015-10-25 Thread Fabien COELHO



[...]

So either the information is important and then should be placed in the 
docs directly, or it's not and then linking to wikipedia is pointless 
because the users are not interested in learning all the details about 
each distribution function.


What is important is that these distributions can be used from pgbench. 
What is a gaussian or an exponential distribution is *not* important as 
such.


For me it is not the point of pg documentation to explain probability 
theory, but just to provide *precise* information about what is actually 
available, for someone who would be interested, without having to read the 
source code. At least that is the idea behind the current documentation.



Firstly, it'd be nice if we could add some figures illustrating the
distributions - much better than explaining the shapes in text. I
don't know if we include figures in the existing docs (probably not),
but generating the figures is rather simple.


There is basically no figures in the documentation. Too bad, but it is
understandable: what should be the format (svg, jpg, png, ...), should
it be generated (gnuplot, others), what is the impact on the
documentation build (html, epub, pdf, ...), how portable should it be,
what about compressed formats vs git diffs?

Once you start asking these questions you understand why there are no
figures:-)


I don't see why diffs would be a problem.


I was not only thinking of mathematical figures, I was also thinking of 
graphics, some format may be zip containing XML stuff for instance.



Probably nitpicking, but left/right of what? I assume the normal
distribution is placed at 0, so it's left/right of zero.


No, it is around the middle of the interval.


You mean [min,max] interval?


Yep.


I believe the transformation

   2.0 * threshold * (i - min - mu + 0.5) / (max - min + 1)

essentially moves the mean into 0, scales the data to [0,1] and then applies 
the threshold.


Probably:-) I wrote that some time ago, and it is 10 pm for me:-).

In other words, the general shape of the curve will be exactly the same no 
matter the actual min/max (except that for longer intervals the values will 
be lower, as there are more possible values).


I don't really see how it's related to this?

   [(max-min)/2 - thresholds, (max-min)/2 + threshold]


The gaussian distribution is about reals, but it is used for integers, so 
there is a projection on integers from the real values. The function 
should compute the probability of drawing a given integer "i" in the 
interval, that is given min, max and threshold, what is the probability of 
drawing i.



Could we simplify the equation a bit? It's needlessly difficult to
realize it's actually just CDF(i+0.5) - CDF(i-0.5). I think it'd be
good to first define the CDF and then just use that.


ISTM that PHI is *the* normal CDF, which is more or less available as
such in various environment (matlab, python, excel...). Well, why not
defined the particular CDF and use it. Not sure the text would be that
much lighter, though.


PHI is the CDF of the normal distribution, not the modified probability 
distribution here (with threshold and scaled to the desired interval).


Yep, that is exactly what I was saying, I think.


This seems broken - too many sentences about the 67% and 95%.


The point is to provide rules of thumb to describe how the distribution
is shaped. Any better sentence is welcome.


Ah, I misread the sentence initially. I haven't realized it speaks about 
1/threshold in the first part, and the second part is an example for 
threshold=4.0. So I thought it's a repetition of the first part.


Maybe it needs spacing and colons and rewording, if it too hard to parse.


Does it make sense to explicitly mention the implementation detail
(Box-Muller transform) here?


No, my point was exactly the opposite - removing the mention of Box-Muller 
entirely, not adding more details about it.


Ok. I think that the fact that it relies on the Box-Muller transform is 
relevant, because there are other methods to generate a gaussian 
distribution, and I would say that there is no reason to have to go to the 
source code to check that. But I would not provide further details. So I'm 
fine with the current status.


--
Fabien.


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


Re: [HACKERS] make Gather node projection-capable

2015-10-25 Thread Simon Riggs
On 22 October 2015 at 16:01, Robert Haas  wrote:

> If we make Gather projection-capable,
> we can just end up with Gather->PartialSeqScan.
>

Is there a reason not to do projection in the Gather node? I don't see one.


> > That said, I don't understand Tom's comment either.  Surely the planner
> > is going to choose to do the projection in the innermost node possible,
> > so that the children nodes are going to do the projections most of the
> > time.  But if for whatever reason this fails to happen, wouldn't it make
> > more sense to do it at Gather than having to put a Result on top?
>
> The planner doesn't seem to choose to do projection in the innermost
> node possible.  The final tlist only gets projected at the top of the
> join tree.  Beneath that, it seems like we project in order to avoid
> carrying Vars through nodes where that would be a needless expense,
> but that's just dropping columns, not computing anything.  That having
> been said, I don't think that takes anything away from your chain of
> reasoning here, and I agree with your conclusion.  There seems to be
> little reason to force a Result node atop a Gather node when we don't
> do that for most other node types.
>

Presumably this is a performance issue then? If we are calculating
something *after* a join which increases rows then the query will be slower
than need be.

I agree the rule should be to project as early as possible.

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: [HACKERS] Proposal: Trigonometric functions in degrees

2015-10-25 Thread David Fetter
On Sun, Oct 25, 2015 at 10:16:41AM +0100, Emre Hasegeli wrote:
> > Currently PostgreSQL only has trigonometric functions that work in
> > radians. I think it would be quite useful to have an equivalent set of
> > functions that worked in degrees. In other environments these are
> > commonly spelled sind(), cosd(), etc.
> 
> I would prefer gradian over degree.

We can have both, but degree is a good bit better known, which means
more users will care about it.

People have gone a long way toward dealing with problems like the
known correspondence (90° = π/2, etc.) and periodicity (f(x) =
f(x+360*n) for integer n, f in (sin, cos, tan, cot, sec, csc), for
example).

I haven't yet found same in a PostgreSQL-compatible library, though.

Cheers,
David.
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


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


Re: [HACKERS] pgbench gaussian/exponential docs improvements

2015-10-25 Thread Fabien COELHO


Hello Tomas,

I've been looking at the checkpoint patches (sorting, flush and FPW 
compensation) and realized we got gaussian/exponential distributions in 
pgbench which is useful for simulating simple non-uniform workloads.


Indeed.

But I think the current docs is a bit too difficult to understand for 
people without deep insight into statistics and shapes of probability 
distributions.


I think the idea is that (1) if you do not know anything distributions, 
probably you do not want expo/gauss, and (2) pg documentation should not 
try to be an introductory course in probability theory.


AFAICR I suggested to point to relevant wikipedia pages but this has been 
more or less rejected, so it ended up as it is, which is indeed pretty 
unconvincing.


Firstly, it'd be nice if we could add some figures illustrating the 
distributions - much better than explaining the shapes in text. I don't 
know if we include figures in the existing docs (probably not), but 
generating the figures is rather simple.


There is basically no figures in the documentation. Too bad, but it is 
understandable: what should be the format (svg, jpg, png, ...), should it 
be generated (gnuplot, others), what is the impact on the documentation 
build (html, epub, pdf, ...), how portable should it be, what about 
compressed formats vs git diffs?


Once you start asking these questions you understand why there are no 
figures:-)



A few more comments:


By default, or when uniform is specified, all values in the range are
drawn with equal probability. Specifying gaussian or exponential
options modifies this behavior; each requires a mandatory threshold
which determines the precise shape of the distribution.


I find the 'threshold' name to be rather unfortunate, as none of the 
probability distribution functions that I know use this term.


I think that it was proposed for gaussian, not sure why.

And even if there's one probability function that uses 'threshold' it 
has very little meaning in the others. For example the exponential 
distribution uses 'rate' (lambda). I'd prefer a neutral name (e.g. 
'parameter').


Why not. Many places to fix, though (documentation & source code).


For a Gaussian distribution, the interval is mapped onto a standard
normal distribution (the classical bell-shaped Gaussian curve)
truncated at -threshold on the left and +threshold on the right.


Probably nitpicking, but left/right of what? I assume the normal 
distribution is placed at 0, so it's left/right of zero.


No, it is around the middle of the interval.


To be precise, if PHI(x) is the cumulative distribution function of
the standard normal distribution, with mean mu defined as (max + min)
/ 2.0, then value i between min and max inclusive is drawn with
probability: (PHI(2.0 * threshold * (i - min - mu + 0.5) / (max -
min + 1)) - PHI(2.0 * threshold * (i - min - mu - 0.5) / (max - min +
1))) / (2.0 * PHI(threshold) - 1.0). Intuitively, the larger the
threshold, the more frequently values close to the middle of the
interval are drawn, and the less frequently values close to the min
and max bounds.


Could we simplify the equation a bit? It's needlessly difficult to realize 
it's actually just CDF(i+0.5) - CDF(i-0.5). I think it'd be good to first 
define the CDF and then just use that.


ISTM that PHI is *the* normal CDF, which is more or less available as such 
in various environment (matlab, python, excel...). Well, why not defined 
the particular CDF and use it. Not sure the text would be that much 
lighter, though.



About 67% of values are drawn from the middle 1.0 / threshold and 95%
in the middle 2.0 / threshold; for instance, if threshold is 4.0, 67%
of values are drawn from the middle quarter and 95% from the middle
half of the interval.


This seems broken - too many sentences about the 67% and 95%.


The point is to provide rules of thumb to describe how the distribution is 
shaped. Any better sentence is welcome.



The minimum threshold is 2.0 for performance of the Box-Muller
transform.


Does it make sense to explicitly mention the implementation detail 
(Box-Muller transform) here?


It is too complex, I would avoid it. I would point to the wikipedia page 
if that could be allowed.


https://en.wikipedia.org/wiki/Box%E2%80%93Muller_transform

--
Fabien.


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


Re: [HACKERS] make Gather node projection-capable

2015-10-25 Thread Robert Haas
On Sun, Oct 25, 2015 at 11:59 AM, Simon Riggs  wrote:
> On 22 October 2015 at 16:01, Robert Haas  wrote:
>> If we make Gather projection-capable,
>> we can just end up with Gather->PartialSeqScan.
>
> Is there a reason not to do projection in the Gather node? I don't see one.

I don't see one either.  There may be some work that needs to be done
to get the projection to happen in the Gather node in all of the cases
where we want it to happen in the Gather node, but that's not an
argument against having the capability.

>> > That said, I don't understand Tom's comment either.  Surely the planner
>> > is going to choose to do the projection in the innermost node possible,
>> > so that the children nodes are going to do the projections most of the
>> > time.  But if for whatever reason this fails to happen, wouldn't it make
>> > more sense to do it at Gather than having to put a Result on top?
>>
>> The planner doesn't seem to choose to do projection in the innermost
>> node possible.  The final tlist only gets projected at the top of the
>> join tree.  Beneath that, it seems like we project in order to avoid
>> carrying Vars through nodes where that would be a needless expense,
>> but that's just dropping columns, not computing anything.  That having
>> been said, I don't think that takes anything away from your chain of
>> reasoning here, and I agree with your conclusion.  There seems to be
>> little reason to force a Result node atop a Gather node when we don't
>> do that for most other node types.
>
> Presumably this is a performance issue then? If we are calculating something
> *after* a join which increases rows then the query will be slower than need
> be.

I don't think there will be a performance issue in most cases because
in most cases the node immediately beneath the Gather node will be
able to do projection, which in most cases is in fact better, because
then the work gets done in the workers.  However, there may be some
cases where it is useful.  After having mulled it over, I think it's
likely that the reason why we didn't introduce a separate node for
projection is that you generally want to project to remove unnecessary
columns at the earliest stage that doesn't lose performance.  So if we
didn't have projection capabilities built into the individual nodes,
then you'd end up with things like Aggregate -> Project -> Join ->
Project -> Scan, which would start to get silly, and likely
inefficient.

> I agree the rule should be to project as early as possible.

Cool.

I'm not sure Tom was really disagreeing with the idea of making Gather
projection-capable ... it seems like he may have just been saying that
there wasn't as much of a rule as I was alleging.  Which is fine: we
can decide what is best here, and I still think this is it.  Barring
further objections, I'm going to commit this, because (1) the status
quo is definitely weird because Gather is abusing the projection stuff
to come up with an extra slot, so doing thing seems unappealing and
(2) I need to make other changes that touch the same areas of the
code, and I want to get this stuff done quickly so that we get a
user-visible feature people can test without writing C code in the
near future.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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


Re: [HACKERS] Freezing without cleanup lock

2015-10-25 Thread Jim Nasby

On 10/22/15 6:39 PM, Alvaro Herrera wrote:

Jim Nasby wrote:


That would be the minimal-impact version, yes. But I suspect if we went
through the trouble to do that, it would be just as easy to attempt the
freeze regardless of what scan_all is set to.


You mean if !scan_all we conditional-get the cleanup lock, if we get it
then prune, if not then freeze?  That seems nice on paper but I think
it's useless because unless scan_all is true, then relfrozenxid doesn't
advance anyway.


Actually, advancing relfrozenxid only depends on having hit all pages in 
the table, which can happen even if !scan_all. Aside from that, once the 
freeze map hits this would be useful in setting bits there.



What I wish I knew is whether this problem was worth worrying about or not.
Hopefully the extra logging in 9.5 will shed some light at some point...


As I recall, Andres says it isn't, but I have recollections of scans
that take a very long time to finish because they keep running into a
vacuum that has a page locked.


I guess lets see if the new logging we have on this sheds some light then.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


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