Re: [HACKERS] Index-only scans with btree_gist

2015-03-28 Thread Andreas Karlsson

On 03/29/2015 03:25 AM, Andreas Karlsson wrote:

On 03/28/2015 09:36 PM, Andreas Karlsson wrote:

Thanks! Do you know if it is possible to add index-only scan support to
range indexes? I have never looked at those and do not know if they are
lossy.


Seems like range types are not compressed at all so implementing
index-only scans was trivial. A patch is attached.


Noticed a couple of typos, so to avoid having them sneak into the code 
here is a version without them.


--
Andreas Karlsson
diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c
index c1ff471..ef84121 100644
--- a/src/backend/utils/adt/rangetypes_gist.c
+++ b/src/backend/utils/adt/rangetypes_gist.c
@@ -216,7 +216,7 @@ range_gist_union(PG_FUNCTION_ARGS)
 	PG_RETURN_RANGE(result_range);
 }
 
-/* compress, decompress are no-ops */
+/* compress, decompress, fetch are no-ops */
 Datum
 range_gist_compress(PG_FUNCTION_ARGS)
 {
@@ -233,6 +233,14 @@ range_gist_decompress(PG_FUNCTION_ARGS)
 	PG_RETURN_POINTER(entry);
 }
 
+Datum
+range_gist_fetch(PG_FUNCTION_ARGS)
+{
+	GISTENTRY  *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
+
+	PG_RETURN_POINTER(entry);
+}
+
 /*
  * GiST page split penalty function.
  *
diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h
index 037684c..8a43f64 100644
--- a/src/include/catalog/pg_amproc.h
+++ b/src/include/catalog/pg_amproc.h
@@ -235,6 +235,7 @@ DATA(insert (	3919   3831 3831 4 3878 ));
 DATA(insert (	3919   3831 3831 5 3879 ));
 DATA(insert (	3919   3831 3831 6 3880 ));
 DATA(insert (	3919   3831 3831 7 3881 ));
+DATA(insert (	3919   3831 3831 9 3996 ));
 
 
 /* gin */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index a96d369..3cd7851 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4951,6 +4951,8 @@ DATA(insert OID = 3877 (  range_gist_compress	PGNSP PGUID 12 1 0 0 0 f f f f t f
 DESCR("GiST support");
 DATA(insert OID = 3878 (  range_gist_decompress PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ range_gist_decompress _null_ _null_ _null_ ));
 DESCR("GiST support");
+DATA(insert OID = 3996 (  range_gist_fetch		PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ range_gist_fetch _null_ _null_ _null_ ));
+DESCR("GiST support");
 DATA(insert OID = 3879 (  range_gist_penalty	PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 2281 "2281 2281 2281" _null_ _null_ _null_ _null_ range_gist_penalty _null_ _null_ _null_ ));
 DESCR("GiST support");
 DATA(insert OID = 3880 (  range_gist_picksplit	PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ range_gist_picksplit _null_ _null_ _null_ ));
diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h
index 173bf74..43c80f4 100644
--- a/src/include/utils/rangetypes.h
+++ b/src/include/utils/rangetypes.h
@@ -209,6 +209,7 @@ extern RangeType *make_empty_range(TypeCacheEntry *typcache);
 extern Datum range_gist_consistent(PG_FUNCTION_ARGS);
 extern Datum range_gist_compress(PG_FUNCTION_ARGS);
 extern Datum range_gist_decompress(PG_FUNCTION_ARGS);
+extern Datum range_gist_fetch(PG_FUNCTION_ARGS);
 extern Datum range_gist_union(PG_FUNCTION_ARGS);
 extern Datum range_gist_penalty(PG_FUNCTION_ARGS);
 extern Datum range_gist_picksplit(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index 35d0dd3..8654e03 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1072,6 +1072,25 @@ select count(*) from test_range_spgist where ir -|- int4range(100,500);
  5
 (1 row)
 
+-- test index-only scans
+explain (costs off)
+select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;
+   QUERY PLAN   
+
+ Sort
+   Sort Key: ir
+   ->  Index Only Scan using test_range_spgist_idx on test_range_spgist
+ Index Cond: (ir -|- '[10,20)'::int4range)
+(4 rows)
+
+select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;
+ ir 
+
+ [20,30)
+ [20,30)
+ [20,10020)
+(3 rows)
+
 RESET enable_seqscan;
 RESET enable_indexscan;
 RESET enable_bitmapscan;
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index aa026ca..af13352 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -286,6 +286,11 @@ select count(*) from test_range_spgist where ir &< int4range(100,500);
 select count(*) from test_range_spgist where ir &> int4range(100,500);
 select count(*) from test_range_spgist where ir -|- int4range(100,500);
 
+-- test index-only scans
+explain (costs off)
+select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;
+select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;

Re: [HACKERS] Index-only scans with btree_gist

2015-03-28 Thread Andreas Karlsson

On 03/28/2015 09:36 PM, Andreas Karlsson wrote:

Thanks! Do you know if it is possible to add index-only scan support to
range indexes? I have never looked at those and do not know if they are
lossy.


Seems like range types are not compressed at all so implementing 
index-only scans was trivial. A patch is attached.


--
Andreas Karlsson
diff --git a/src/backend/utils/adt/rangetypes_gist.c b/src/backend/utils/adt/rangetypes_gist.c
index c1ff471..dea8f04 100644
--- a/src/backend/utils/adt/rangetypes_gist.c
+++ b/src/backend/utils/adt/rangetypes_gist.c
@@ -216,7 +216,7 @@ range_gist_union(PG_FUNCTION_ARGS)
 	PG_RETURN_RANGE(result_range);
 }
 
-/* compress, decompress are no-ops */
+/* compress, decompress, fecth are no-ops */
 Datum
 range_gist_compress(PG_FUNCTION_ARGS)
 {
@@ -233,6 +233,14 @@ range_gist_decompress(PG_FUNCTION_ARGS)
 	PG_RETURN_POINTER(entry);
 }
 
+Datum
+range_gist_fetch(PG_FUNCTION_ARGS)
+{
+	GISTENTRY  *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
+
+	PG_RETURN_POINTER(entry);
+}
+
 /*
  * GiST page split penalty function.
  *
diff --git a/src/include/catalog/pg_amproc.h b/src/include/catalog/pg_amproc.h
index 037684c..e80c897 100644
--- a/src/include/catalog/pg_amproc.h
+++ b/src/include/catalog/pg_amproc.h
@@ -235,6 +235,7 @@ DATA(insert (	3919   3831 3831 4 3878 ));
 DATA(insert (	3919   3831 3831 5 3879 ));
 DATA(insert (	3919   3831 3831 6 3880 ));
 DATA(insert (	3919   3831 3831 7 3881 ));
+DATA(insert (   3919   3831 3831 9 3996 ));
 
 
 /* gin */
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index a96d369..3cd7851 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -4951,6 +4951,8 @@ DATA(insert OID = 3877 (  range_gist_compress	PGNSP PGUID 12 1 0 0 0 f f f f t f
 DESCR("GiST support");
 DATA(insert OID = 3878 (  range_gist_decompress PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ range_gist_decompress _null_ _null_ _null_ ));
 DESCR("GiST support");
+DATA(insert OID = 3996 (  range_gist_fetch		PGNSP PGUID 12 1 0 0 0 f f f f t f i 1 0 2281 "2281" _null_ _null_ _null_ _null_ range_gist_fetch _null_ _null_ _null_ ));
+DESCR("GiST support");
 DATA(insert OID = 3879 (  range_gist_penalty	PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 2281 "2281 2281 2281" _null_ _null_ _null_ _null_ range_gist_penalty _null_ _null_ _null_ ));
 DESCR("GiST support");
 DATA(insert OID = 3880 (  range_gist_picksplit	PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ range_gist_picksplit _null_ _null_ _null_ ));
diff --git a/src/include/utils/rangetypes.h b/src/include/utils/rangetypes.h
index 173bf74..43c80f4 100644
--- a/src/include/utils/rangetypes.h
+++ b/src/include/utils/rangetypes.h
@@ -209,6 +209,7 @@ extern RangeType *make_empty_range(TypeCacheEntry *typcache);
 extern Datum range_gist_consistent(PG_FUNCTION_ARGS);
 extern Datum range_gist_compress(PG_FUNCTION_ARGS);
 extern Datum range_gist_decompress(PG_FUNCTION_ARGS);
+extern Datum range_gist_fetch(PG_FUNCTION_ARGS);
 extern Datum range_gist_union(PG_FUNCTION_ARGS);
 extern Datum range_gist_penalty(PG_FUNCTION_ARGS);
 extern Datum range_gist_picksplit(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/expected/rangetypes.out b/src/test/regress/expected/rangetypes.out
index 35d0dd3..8654e03 100644
--- a/src/test/regress/expected/rangetypes.out
+++ b/src/test/regress/expected/rangetypes.out
@@ -1072,6 +1072,25 @@ select count(*) from test_range_spgist where ir -|- int4range(100,500);
  5
 (1 row)
 
+-- test index-only scans
+explain (costs off)
+select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;
+   QUERY PLAN   
+
+ Sort
+   Sort Key: ir
+   ->  Index Only Scan using test_range_spgist_idx on test_range_spgist
+ Index Cond: (ir -|- '[10,20)'::int4range)
+(4 rows)
+
+select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;
+ ir 
+
+ [20,30)
+ [20,30)
+ [20,10020)
+(3 rows)
+
 RESET enable_seqscan;
 RESET enable_indexscan;
 RESET enable_bitmapscan;
diff --git a/src/test/regress/sql/rangetypes.sql b/src/test/regress/sql/rangetypes.sql
index aa026ca..af13352 100644
--- a/src/test/regress/sql/rangetypes.sql
+++ b/src/test/regress/sql/rangetypes.sql
@@ -286,6 +286,11 @@ select count(*) from test_range_spgist where ir &< int4range(100,500);
 select count(*) from test_range_spgist where ir &> int4range(100,500);
 select count(*) from test_range_spgist where ir -|- int4range(100,500);
 
+-- test index-only scans
+explain (costs off)
+select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;
+select ir from test_range_spgist where ir -|- int4range(10,20) order by ir;
+
 RESET enable_seqscan;
 RESET enable_indexscan;
 RESET enable_bitmapscan;

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To 

Re: [HACKERS] Rounding to even for numeric data type

2015-03-28 Thread Gavin Flower

On 29/03/15 13:07, David G. Johnston wrote:
On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier 
mailto:michael.paqu...@gmail.com>>wrote:


On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
mailto:gavinflo...@archidevsys.co.nz>> wrote:
> On 28/03/15 21:58, Dean Rasheed wrote:
> [...]
>>
>>
>> Andrew mentioned that there have been complaints from people doing
>> calculations with monetary data that we don't implement
>> round-to-nearest-even (Banker's) rounding. It's actually the
case that
>> various different financial calculations demand different specific
>> rounding modes, so it wouldn't be enough to simply change the
default
>> - we would have to provide a choice of modes.
>
> [...]
>
> Could the 2 current round functions have cousins that included
an extra char
> parameter (or string), that indicated the type of rounding? 


>
> So we don't end up with an explosion of rounding functions, yet
could cope
> with a limited set of additional rounding modes initially, and
possibly
> others in the future.

Instead of extending round, isn't what we are looking at here a new
data type? I have doubts that we only want to have a way to switch
round() between different modes. Hence, what we could do is:
1) Mention in the docs that numeric does round-half-away-from-zero
2) Add regression tests for numeric(n,m) and round(numeric)
3) Add a TODO item for something like numeric2, doing rounding-at-even
(this could be an extension as well), but with the number of
duplication that it may have with numeric, an in-core type would make
sense, to facilitate things exposing some of structures key structures
would help.


So, create a numeric type for each possible rounding mode​? That 
implies at least two types, round-half-even and 
round-half-away-from-zero, with suitable abbreviations: numeric_rhe, 
numeric_raz.


If the goal is to make plain numeric IEEE standard conforming then 
giving the user a way to switch all existing numeric types to 
numeric_raz would be nice.


Implicit casts between each of the various numeric types would be 
needed and understandable.


I'm pondering calling them numeric_eng and numeric_bus (for 
engineering and business respectively)...


David J.


In Java, there are 8 rounding modes specified:

https://docs.oracle.com/javase/8/docs/api/java/math/RoundingMode.html

Some of these may be relevant to pg.


Cheers,
Gavin


--
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] Rounding to even for numeric data type

2015-03-28 Thread David G. Johnston
On Sat, Mar 28, 2015 at 3:59 PM, Michael Paquier 
wrote:

> On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
>  wrote:
> > On 28/03/15 21:58, Dean Rasheed wrote:
> > [...]
> >>
> >>
> >> Andrew mentioned that there have been complaints from people doing
> >> calculations with monetary data that we don't implement
> >> round-to-nearest-even (Banker's) rounding. It's actually the case that
> >> various different financial calculations demand different specific
> >> rounding modes, so it wouldn't be enough to simply change the default
> >> - we would have to provide a choice of modes.
> >
> > [...]
> >
> > Could the 2 current round functions have cousins that included an extra
> char
> > parameter (or string), that indicated the type of rounding?

>
> > So we don't end up with an explosion of rounding functions, yet could
> cope
> > with a limited set of additional rounding modes initially, and possibly
> > others in the future.
>
> Instead of extending round, isn't what we are looking at here a new
> data type? I have doubts that we only want to have a way to switch
> round() between different modes. Hence, what we could do is:
> 1) Mention in the docs that numeric does round-half-away-from-zero
> 2) Add regression tests for numeric(n,m) and round(numeric)
> 3) Add a TODO item for something like numeric2, doing rounding-at-even
> (this could be an extension as well), but with the number of
> duplication that it may have with numeric, an in-core type would make
> sense, to facilitate things exposing some of structures key structures
> would help.
>
>
So, create a numeric type for each possible rounding mode​?  That implies
at least two types, round-half-even and round-half-away-from-zero, with
suitable abbreviations: numeric_rhe, numeric_raz.

If the goal is to make plain numeric IEEE standard conforming then giving
the user a way to switch all existing numeric types to numeric_raz would be
nice.

Implicit casts between each of the various numeric types would be needed
and understandable.

I'm pondering calling them numeric_eng and numeric_bus (for engineering and
business respectively)...

David J.


Re: [HACKERS] compute_index_stats is missing a CHECK_FOR_INTERRUPTS

2015-03-28 Thread Jeff Janes
On Sat, Mar 28, 2015 at 3:37 PM, Tom Lane  wrote:

> Jeff Janes  writes:
> > Analyze on functional indexes cannot be interrupted very easily.
> > ...
> > The attached patch fixes it, but don't vouch for its safety.
>
> Hm.  The other per-sample-row loops in analyze.c use vacuum_delay_point()
> rather than CHECK_FOR_INTERRUPTS() directly.  Ordinarily that wouldn't
> make much difference here, but maybe a slow index function might be
> incurring I/O?
>

That isn't the case for me (and if it were, they wouldn't be going through
the buffer manager anyway and so would not trigger delay criteria), but
that seems like a valid concern in general.  It also explains why I
couldn't find CHECK_FOR_INTERRUPTS in other loops of that file, because I
was looking for the wrong spelling.

Adding a vacuum_delay_point does solve the immediately observed problem,
both the toy one and the more realistic one.

Thanks,

Jeff


compute_index_stats_vacdelay.patch
Description: Binary data

-- 
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] Rounding to even for numeric data type

2015-03-28 Thread Michael Paquier
On Sun, Mar 29, 2015 at 5:34 AM, Gavin Flower
 wrote:
> On 28/03/15 21:58, Dean Rasheed wrote:
> [...]
>>
>>
>> Andrew mentioned that there have been complaints from people doing
>> calculations with monetary data that we don't implement
>> round-to-nearest-even (Banker's) rounding. It's actually the case that
>> various different financial calculations demand different specific
>> rounding modes, so it wouldn't be enough to simply change the default
>> - we would have to provide a choice of modes.
>
> [...]
>
> Could the 2 current round functions have cousins that included an extra char
> parameter (or string), that indicated the type of rounding?
>
> So we don't end up with an explosion of rounding functions, yet could cope
> with a limited set of additional rounding modes initially, and possibly
> others in the future.

Instead of extending round, isn't what we are looking at here a new
data type? I have doubts that we only want to have a way to switch
round() between different modes. Hence, what we could do is:
1) Mention in the docs that numeric does round-half-away-from-zero
2) Add regression tests for numeric(n,m) and round(numeric)
3) Add a TODO item for something like numeric2, doing rounding-at-even
(this could be an extension as well), but with the number of
duplication that it may have with numeric, an in-core type would make
sense, to facilitate things exposing some of structures key structures
would help.

Regards,
-- 
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] How about to have relnamespace and relrole?

2015-03-28 Thread Andrew Dunstan


On 03/10/2015 04:42 AM, Kyotaro HORIGUCHI wrote:

Thank you for the correction.

At Wed, 4 Mar 2015 01:01:48 -0600, Jim Nasby  wrote in 
<54f6addc.8030...@bluetreble.com>

On 3/3/15 8:04 PM, Kyotaro HORIGUCHI wrote:

Note: The OID alias types don't sctrictly comply the transaction
   isolation rules so do not use them where exact transaction
   isolation on the values of these types has a
   significance. Likewise, since they look as simple constants to
   planner so you might get slower plans than the queries joining
   the system tables correnspond to the OID types.

Might I suggest:

Note: The OID alias types do not completely follow transaction
isolation rules. The planner also treats them as simple constants,
which may result in sub-optimal planning.

Looks far simple and enough.
The note has been replaced with your sentence in the attached patch.





I have just claimed this as committer in the CF, but on reviewing the 
emails it looks like there is disagreement about the need for it at all, 
especially from Tom and Robert.


I confess I have often wanted regnamespace, particularly, and 
occasionally regrole, simply as a convenience. But I'm not going to 
commit it against substantial opposition.


Do we need a vote?

cheers

andrew


--
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: row_to_array function

2015-03-28 Thread Jeff Janes
On Tue, Jan 27, 2015 at 10:58 AM, Pavel Stehule 
wrote:

> Hi
>
> 2015-01-27 11:41 GMT+01:00 Pavel Stehule :
>
>>
>>
>> 2015-01-26 21:44 GMT+01:00 Jim Nasby :
>>
>>> On 1/25/15 4:23 AM, Pavel Stehule wrote:
>>>

 I tested a concept iteration over array in format [key1, value1, key2,
 value2, .. ] - what is nice, it works for [[key1,value1],[key2, value2],
 ...] too

 It is only a few lines more to current code, and this change doesn't
 break a compatibility.

 Do you think, so this patch is acceptable?

 Ideas, comments?

>>>
>>> Aside from fixing the comments... I think this needs more tests on
>>> corner cases. For example, what happens when you do
>>>
>>> foreach a, b, c in array(array(1,2),array(3,4)) ?
>>>
>>
>> it is relative simple behave -- empty values are NULL
>>
>> array(1,2),array(3,4) -- do you think ARRAY[[1,2],[3,4]] is effectively
>> ARRAY[1,2,3,4]
>>
>>
>>>
>>> Or the opposite case of
>>>
>>> foreach a,b in array(array(1,2,3))
>>>
>>> Also, what about:
>>>
>>> foreach a,b in '{{{1,2},{3,4}},{{5,6},{7,8}}}'::int[] ?
>>
>>
>>
>>  postgres=# select array(select
>> unnest('{{{1,2},{3,4}},{{5,6},{7,8}}}'::int[]));
>>array
>> ---
>>  {1,2,3,4,5,6,7,8}
>> (1 row)
>>
>> so it generate pairs {1,2}{3,4},{5,6},{7,8}
>>
>
> I fixed situation when array has not enough elements.
>


This no longer applies due to conflicts in src/pl/plpgsql/src/pl_exec.c
caused by e524cbdc45ec6d677b1dd49

Also, what is the relationship of this patch to the row_to_array patch?
Are they independent, or does one depend on the other?  row_to_array by
itself applies but doesn't compile.

Cheers,

Jeff


Re: [HACKERS] PATCH: pgbench - merging transaction logs

2015-03-28 Thread Tomas Vondra
On 28.3.2015 11:21, Fabien COELHO wrote:
> 
> Hello Tomas,
> 
>> I do agree that fprintf is not cheap, actually when profiling pgbench
>> it's often the #1 item, but the impact on the measurements is actually
>> quite small. For example with a small database (scale 10) and read-only
>> 30-second runs (single client), I get this:
>>
>>   no logging: 18672 18792 18667 18518 18613 18547
>> with logging: 18170 18093 18162 18273 18307 18234
>>
>> So on average, that's 18634 vs. 18206, i.e. less than 2.5% difference.
>> And with more expensive transactions (larger scale, writes, ...) the
>> difference will be much smaller.
> 
> I did some testing with a scale 10 prepared "SELECT only" 200 seconds
> plenty of runs with local socket connections on the largest host I have
> available:
> 
>   pgbench -P 10 -T 200 -S -M prepared -j $c -c $c ...
> 
> I think that this cpu-bound bench is kind of a worst possible case for
> the detailed per transaction log.
> 
> I also implemented a quick and dirty version for a merge log based on
> sharing a file handle (append mode + sprintf + fputs).
> 
> The results are as follow:
> 
>  * 1 thread 33 runs median tps (average is consistent):
>  - no logging:22062
>  - separate logging:  19360  (-12.2%)
>  - merged logging:19326  (-12.4%, not significant from previous)
> 

Interesting. What hardware is this? I wouldn't be surprised by this
behavior on a multi-socket system, where the cross-cpu transfers are
likely more expensive on the single-socket I used (sorry, forgot to
mention what hardware I used).

> Note that the impact of logging is much larger than with your tests.
> The underlying fprintf comes from gnu libc 2.19.

I'm running glibc 2.19 too, on kernel 3.17 (might be important too).

> The worst overhead I could trigger is with 12 threads:
> 
>  * 12 threads 35 runs median tps (average is consistent)
>  - no logging:   155917
>  - separate logging: 124695 (-20.0%)
>  - merged logging:   119349 (-23.5%)
> 
> My conclusion from these figures is that although the direct merged 
> logging approach adds some overhead, this overhead is small wrt
> detailed logging (it adds 3.5% to a 20% logging overhead) with 12
> threads. Other tests, even with more threads, did not yield larger
> absolute or relative overheads. Although the direct merge approach is
> shown to add overheads, this is a small additional overhead on a
> quite bad situation already, which suggest that using detailed log on
> a cpu-bound pgbench run is a bad idea to begin with.

Yeah, seems like that.

> For a more realistic test, I ran "simple updates" which involve actual
> disk writes. It ran at around 840 tps with 24 threads. The logging
> overhead seems to be under 1%, and there is no significant difference
> between separate and merged on the 20 runs.
> 
> So my overall conclusion is:
> 
> (1) The simple thread-shared file approach would save pgbench from 
> post-processing merge-sort heavy code, for a reasonable cost.

No it wouldn't - you're missing the fact that the proposed approach
(shared file + fprintf) only works with raw transaction log.

It does not work with aggregated log - the threads would have to somehow
track the progress of the other threads somehow, in a very non-trivial
way (e.g. what if one of the threads executes a long query, and thus
does not send the results for a long time?). Another option would be to
update shared aggregated results, but that requires locking.

> (2) The feature would not be available for the thread-emulation with 
> this approach, but I do not see this as a particular issue as I
> think that it is pretty much only dead code and a maintenance burden.

I'm not willing to investigate that, nor am I willing to implement
another feature that works only sometimes (I've done that in the past,
and I find it a bad practice).

If someone else is willing to try to eliminate the thread emulation, I
won't object to that. But as I pointed out above, simple fprintf is not
going to work for the aggregated log - solving that will need more code
(e.g. maintaining aggregated results for all threads, requiring
additional locking etc).

> 
> (3) Optimizing doLog from its current fprintf-based implementation
> may be a good thing.

That's probably true. The simplest thing we can do right now is
buffering the data into larger chunks and writing those chunks. That
amortizes the costs of locking. Using O_APPEND, as suggested by Andres,
seems like a promising idea.

-- 
Tomas Vondrahttp://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] Replication identifiers, take 4

2015-03-28 Thread Petr Jelinek

So I did some more in depth look, I do have couple of comments.

I would really like to have something like "Logical Replication 
Infrastructure" doc section that would have both decoding and 
identifiers (and possibly even CommitTs) underneath.


There is typo in docs:

+ 
+   The optional filter_by_origin_cb callback
+   is called to determine wheter data that has been replayed


wheter -> whether


And finally I have issue with how the new identifiers are allocated. 
Currently, if you create identifier 'foo', remove identifier 'foo' and 
create identifier 'bar', the identifier 'bar' will have same id as the 
old 'foo' identifier. This can be problem because the identifier id is 
used as origin of the data and the replication solution using the 
replication identifiers can end up thinking that data came from node 
'bar' even though they came from the node 'foo' which no longer exists. 
This can have bad effects for example on conflict detection or debugging 
problems with replication.


Maybe another reason to use standard Oids?

--
 Petr Jelinek  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


Re: [HACKERS] compute_index_stats is missing a CHECK_FOR_INTERRUPTS

2015-03-28 Thread Tom Lane
Jeff Janes  writes:
> Analyze on functional indexes cannot be interrupted very easily.
> ...
> The attached patch fixes it, but don't vouch for its safety.

Hm.  The other per-sample-row loops in analyze.c use vacuum_delay_point()
rather than CHECK_FOR_INTERRUPTS() directly.  Ordinarily that wouldn't
make much difference here, but maybe a slow index function might be
incurring I/O?

regards, tom lane


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


[HACKERS] compute_index_stats is missing a CHECK_FOR_INTERRUPTS

2015-03-28 Thread Jeff Janes
Analyze on functional indexes cannot be interrupted very easily.

Example:

create language plperl;
create table foo1 as select x::text from generate_series(1,1000) foo (x);
create table foo2 as select reverse(x) from foo1;
--use a fast version to set up the demo, as we are impatient
CREATE or replace FUNCTION slow_reverse(text) RETURNS text
LANGUAGE plperl IMMUTABLE STRICT COST 100
AS $_X$
  return reverse($_[0]);
$_X$;
create index on foo2 (slow_reverse(reverse));
analyze foo2;
--put the slow version in place.
CREATE or replace FUNCTION slow_reverse(text) RETURNS text
LANGUAGE plperl IMMUTABLE STRICT COST 100
AS $_X$
  my $foo; foreach (1..1e6) {$foo+=sqrt($_)};
  return reverse($_[0]);
$_X$;

-- now spring the trap
analyze foo2;

Ctrl-C (or pg_ctl stop -mf) hangs for a long time.

The attached patch fixes it, but don't vouch for its safety.

I believe I've seen a real-world example of this causing refusal of a fast
shutdown to shutdown fast.

Cheers,

Jeff


compute_index_stats_interrupt.patch
Description: Binary data

-- 
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] Index-only scans with btree_gist

2015-03-28 Thread Andreas Karlsson

On 03/28/2015 02:12 PM, Heikki Linnakangas wrote:

Looks good to me. Committed, thanks.


Thanks! Do you know if it is possible to add index-only scan support to 
range indexes? I have never looked at those and do not know if they are 
lossy.


Andreas


--
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] Rounding to even for numeric data type

2015-03-28 Thread Gavin Flower

On 28/03/15 21:58, Dean Rasheed wrote:
[...]


Andrew mentioned that there have been complaints from people doing
calculations with monetary data that we don't implement
round-to-nearest-even (Banker's) rounding. It's actually the case that
various different financial calculations demand different specific
rounding modes, so it wouldn't be enough to simply change the default
- we would have to provide a choice of modes.

[...]

Could the 2 current round functions have cousins that included an extra 
char parameter (or string), that indicated the type of rounding?


So we don't end up with an explosion of rounding functions, yet could 
cope with a limited set of additional rounding modes initially, and 
possibly others in the future.



Cheers,
Gavin




--
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] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

2015-03-28 Thread Andres Freund
Hi,

Just had a longer chat with Peter about this patch.

* Not a fan of the heap flags usage, the reusage seems sketch to me
* Explain should show the arbiter index in text as well
* AddUniqueSpeculative is a bad name, it should refer IndexInfo
* Work on the ExecInsert() comments
* Let's remove the planner choosing the "cheapest" arbiter index; it
  should do all.
* s/infer_unique_index/infer_arbiter_index/
* Not supporting inheritance properly makes me uncomfortable. I don't
  think users will think that's a acceptable/reasonable restriction.
* Let's not use t_ctid directly, but add a wrapper
* The code uses LockTupleExclusive to lock rows. That means the fkey key
  locking doesn't work; That's annoying. This means that using upsert
  will potentially cause deadlocks in other sessions :(. I think you'll
  have to determine what lock to acquire by fetching the tuple, doing
  the key comparison, and acquire the appropriate lock. That should be
  fine.
* I think we should decouple the insertion and wal logging more. I think
  the promise tuple insertion should be different from the final
  insertion of the actual tuple. For one it seems cleaner to me, for
  another it will avoid the uglyness around logical decoding. I think
  also that the separation will make it more realistic to use something
  like this for a COPY variant that doesn't raise unique violations and
  such.
* We discussed the infererence and that it should just reuse (code,
  grammar, docs) the column specification from create index.
* Some more stuff I don't recall.


Greetings,

Andres Freund

-- 
 Andres Freund 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


Re: [HACKERS] Rounding to even for numeric data type

2015-03-28 Thread Kevin Grittner
Dean Rasheed  wrote:

> Sure, using round-to-nearest-even for intermediate rounding in
> complex numeric methods would be a good way to reduce (but not
> completely eliminate) rounding errors. But that's a somewhat
> different proposition from changing the default for round(),
> which is a much more user-visible change. If we did implement a
> choice of rounding modes, I would still argue for keeping
> round-half-away-from-zero as the default mode for round().

I'm inclined to agree.  In business software development, that's
how I've seen the "stakeholder" expectations.  Thinking back, I can
remember dealing with rounding in manufacturing incentive pay
calculation, interfacing long-range demand forcasting to production
planning, interfacing engineers' CAD/CAM software to IBM MAPICS,
professionals' timekeeping/billing/AR systems, and various general
accounting software systems; and as I seem to remember those
efforts, round half away from zero has always been when end users
understood and expected when explicitly rounding a final result.

I understand how rounding half to even in intermediate results
minimizes rounding error, and would not be surprised to see some
users with different expectations, but there is clearly a large
base of people who would be surprised by it when rounding a final
result.

> I also agree with Andrew that all numeric functions should be
> kept immutable.

Which means no GUC should affect how it behaves, although a
function with a parameter to control rounding behavior would be OK.

It kinda seems like the SQL round() function should have a
parameter to control this which defaults to the historical behavior
when omitted.

--
Kevin Grittner
EDB: 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] Index-only scans with btree_gist

2015-03-28 Thread Heikki Linnakangas

On 03/28/2015 01:14 AM, Andreas Karlsson wrote:

On 03/26/2015 10:31 PM, Heikki Linnakangas wrote:

I've pushed Anastasia's patch to support index-only scans with GiST, and
it's time to add opclasses support for all the opclasses that are not
lossy. I think at least all the btree_gist opclasses need to be
supported, it would be pretty surprising if they didn't support
index-only scans, while some more complex opclasses did.

Attached is a WIP patch for that. It covers all the varlen types that
btree_gist supports, and int2, int4 and oid. The rest of the fixed-width
types should be just a matter of copy-pasting. I'll continue adding
those, but wanted to let people know I'm working on this.


Would it also be worth doing the same for the inet_ops class for
inet/cidr? I have hacked a quick WIP patch which I believe should work,
but have not looked into the index only scan code enough to be sure.


Looks good to me. Committed, thanks.

- Heikki


--
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] Rounding to even for numeric data type

2015-03-28 Thread Michael Paquier
On Sat, Mar 28, 2015 at 5:58 PM, Dean Rasheed wrote:
> On 27 March 2015 at 23:26, Tom Lane wrote:
>> I think the concern over backwards compatibility here is probably
>> overblown; but if we're sufficiently worried about it, a possible
>> compromise is to invent a numeric_rounding_mode GUC, so that people
>> could get back the old behavior if they really care.
>>
>
> Backwards compatibility is certainly one concern. Michael also
> mentioned compatibility with other databases, and its worth noting
> that Oracle, MySQL, DB2 and SQL Server all use the same default
> round-half-away-from-zero "Schoolbook" rounding mode in round() for
> their equivalents of numeric. Most of those other DBs are also careful
> to document exactly how round() behaves. To make our round() function
> do something different by default isn't going to make porting any
> easier.

I was not aware of that, and that's really an interesting point.
Thanks! It would indeed not be welcome for people migrating an
application to Postgres if we behave differently from the others.
Then, perhaps the solution would be to have this rounding GUC, but
pointing by default to round-half-away-from-zero and not round-to-even
as mentioned upthread already.

> Andrew mentioned that there have been complaints from people doing
> calculations with monetary data that we don't implement
> round-to-nearest-even (Banker's) rounding. It's actually the case that
> various different financial calculations demand different specific
> rounding modes, so it wouldn't be enough to simply change the default
> - we would have to provide a choice of modes. I also agree with Andrew
> that all numeric functions should be kept immutable.

This looks like a plan. Honestly by reading this thread the thing that
IMO we should not do is closing ourselves into a single mode of
calculation.
-- 
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] PATCH: pgbench - merging transaction logs

2015-03-28 Thread Fabien COELHO


Hello Tomas,


I do agree that fprintf is not cheap, actually when profiling pgbench
it's often the #1 item, but the impact on the measurements is actually
quite small. For example with a small database (scale 10) and read-only
30-second runs (single client), I get this:

  no logging: 18672 18792 18667 18518 18613 18547
with logging: 18170 18093 18162 18273 18307 18234

So on average, that's 18634 vs. 18206, i.e. less than 2.5% difference.
And with more expensive transactions (larger scale, writes, ...) the
difference will be much smaller.


I did some testing with a scale 10 prepared "SELECT only" 200 seconds 
plenty of runs with local socket connections on the largest host I have 
available:


  pgbench -P 10 -T 200 -S -M prepared -j $c -c $c ...

I think that this cpu-bound bench is kind of a worst possible case for the 
detailed per transaction log.


I also implemented a quick and dirty version for a merge log based on 
sharing a file handle (append mode + sprintf + fputs).


The results are as follow:

 * 1 thread 33 runs median tps (average is consistent):
 - no logging:22062
 - separate logging:  19360  (-12.2%)
 - merged logging:19326  (-12.4%, not significant from previous)

Note that the impact of logging is much larger than with your tests.
The underlying fprintf comes from gnu libc 2.19.

The worst overhead I could trigger is with 12 threads:

 * 12 threads 35 runs median tps (average is consistent)
 - no logging:   155917
 - separate logging: 124695 (-20.0%)
 - merged logging:   119349 (-23.5%)

My conclusion from these figures is that although the direct merged 
logging approach adds some overhead, this overhead is small wrt detailed 
logging (it adds 3.5% to a 20% logging overhead) with 12 threads. Other 
tests, even with more threads, did not yield larger absolute or relative 
overheads. Although the direct merge approach is shown to add overheads, 
this is a small additional overhead on a quite bad situation already, 
which suggest that using detailed log on a cpu-bound pgbench run is a bad 
idea to begin with.


For a more realistic test, I ran "simple updates" which involve actual 
disk writes. It ran at around 840 tps with 24 threads. The logging 
overhead seems to be under 1%, and there is no significant difference 
between separate and merged on the 20 runs.


So my overall conclusion is:

(1) The simple thread-shared file approach would save pgbench from 
post-processing merge-sort heavy code, for a reasonable cost.


(2) The feature would not be available for the thread-emulation with this 
approach, but I do not see this as a particular issue as I think that it 
is pretty much only dead code and a maintenance burden.


(3) Optimizing doLog from its current fprintf-based implementation may be 
a good thing.


--
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] Rounding to even for numeric data type

2015-03-28 Thread Dean Rasheed
On 28 March 2015 at 05:16, Andrew Gierth  wrote:
>> "Tom" == Tom Lane  writes:
>
>  Tom> I think the concern over backwards compatibility here is probably
>  Tom> overblown; but if we're sufficiently worried about it, a possible
>  Tom> compromise is to invent a numeric_rounding_mode GUC, so that
>  Tom> people could get back the old behavior if they really care.
>
> I only see one issue with this, but it's a nasty one: do we really want
> to make all numeric operations that might do rounding stable rather than
> immutable?
>

Yeah, making all numeric functions non-immutable seems like a really bad idea.

Regards,
Dean


-- 
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] Rounding to even for numeric data type

2015-03-28 Thread Dean Rasheed
On 27 March 2015 at 23:26, Tom Lane  wrote:
> Michael Paquier  writes:
>> It sounds appealing to switch the default behavior to something that
>> is more IEEE-compliant, and not only for scale == 0. Now one can argue
>> as well that changing the default is risky for existing applications,
>> or the other way around that other RDBMs (?) are more compliant than
>> us for their equivalent numeric data type, and people get confused
>> when switching to Postgres.
>
>> An idea, from Dean, would be to have a new specific version for
>> round() able to do compliant IEEE rounding to even as well...
>
> I think confining the change to round() would be a fundamental error.
> The main reason why round-to-nearest-even is IEEE standard is that it
> reduces error accumulation over long chains of calculations, such as
> in numeric's power and trig functions; if we go to the trouble of
> implementing such a behavior, we certainly want to use it there.
>

Sure, using round-to-nearest-even for intermediate rounding in complex
numeric methods would be a good way to reduce (but not completely
eliminate) rounding errors. But that's a somewhat different
proposition from changing the default for round(), which is a much
more user-visible change. If we did implement a choice of rounding
modes, I would still argue for keeping round-half-away-from-zero as
the default mode for round().

> I think the concern over backwards compatibility here is probably
> overblown; but if we're sufficiently worried about it, a possible
> compromise is to invent a numeric_rounding_mode GUC, so that people
> could get back the old behavior if they really care.
>

Backwards compatibility is certainly one concern. Michael also
mentioned compatibility with other databases, and its worth noting
that Oracle, MySQL, DB2 and SQL Server all use the same default
round-half-away-from-zero "Schoolbook" rounding mode in round() for
their equivalents of numeric. Most of those other DBs are also careful
to document exactly how round() behaves. To make our round() function
do something different by default isn't going to make porting any
easier.

Andrew mentioned that there have been complaints from people doing
calculations with monetary data that we don't implement
round-to-nearest-even (Banker's) rounding. It's actually the case that
various different financial calculations demand different specific
rounding modes, so it wouldn't be enough to simply change the default
- we would have to provide a choice of modes. I also agree with Andrew
that all numeric functions should be kept immutable.

Regards,
Dean


-- 
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] Performance improvement for joins where outer side is unique

2015-03-28 Thread David Rowley
On 25 March 2015 at 01:11, Kyotaro HORIGUCHI <
horiguchi.kyot...@lab.ntt.co.jp> wrote:

> Hi, thanks for the new patch.
>
> I made an additional shrink from your last one. Do you have a
> look on the attached?
>
>
Thanks, for looking again.

I'm not too sure I like the idea of relying on join removals to mark the
is_unique_join property.
By explicitly doing it in mark_unique_joins we have the nice side effect of
not having to re-check a relations unique properties after removing another
relation, providing the relation was already marked as unique on the first
pass.


> At Sun, 22 Mar 2015 19:42:21 +1300, David Rowley 
> wrote in <
> caaphdvrkwmmtwkxfn4uazyza9jql1c7uwbjbtuwfr69rqlv...@mail.gmail.com>
> > On 20 March 2015 at 21:11, David Rowley  wrote:
> > >
> > > I can continue working on your patch if you like? Or are you planning
> to
> > > go further with it?
>
> It's fine that you continue to work on this.
>
> # Sorry for the hardly baked patch which had left many things alone:(
>
> > I've been working on this more over the weekend and I've re-factored
> things
> > to allow LEFT JOINs to be properly marked as unique.
> > I've also made changes to re-add support for detecting the uniqueness of
> > sub-queries.
>
> I don't see the point of calling mark_unique_joins for every
> iteration on join_info_list in remove_useless_joins.
>
>
I've fixed this in the attached. I must have forgotten to put the test for
LEFT JOINs here as I was still thinking that I might make a change to the
code that converts unique semi joins to inner joins so that it just checks
is_unique_join instead of calling relation_has_unique_index_for().


> The loop already iteraltes on whole the join_info_list so
> mark_unique_join as an individual function is needless.
>
> Finally, simply marking uniqueness of join in join_is_removable
> seems to be enough, inhibiting early bailing out by the checks on
> attribute usage and placeholder let it work as expected.
>
> Reducing changes to this extent, I can easily see what is added
> to planning computations. It consists of mainly two factors.
>
> - Unique-join chekcs for every candidate inner joins in
>   add_paths_to_joinrel.
>
> - Uniqueness check of mergejoinable clause in join-removability
>   check for every left join, some of which would be skipped
>   by other checks before.
>
>
> > Also, I've added modified the costing for hash and nested loop joins to
> > reduce the cost for unique inner joins to cost the join the same as it
> does
> > for SEMI joins. This has tipped the scales on a few plans in the
> regression
> > tests.
>
> I've forgotten it, but quite important.
>
>
I've fixed quite a fundamental bug in my previous costing change. The fix
for this makes it so I have to pass the unique_inner bool down to the
costing functions. This also changes how the JoinPaths are marked as
unique_inner. This is now done when the JoinPath is created, instead of
updating it afterwards like it was done previously.

> Also, please see attached unijoin_analysis.patch. This just adds some code
> > which spouts out notices when join nodes are initialised which states if
> > the join is unique or not. Running the regression tests with this patch
> in
> > places gives:
> >
> > Unique Inner: Yes == 753 hits
> > Unique Inner: No == 1430 hits
> >
> > So it seems we can increase the speed of about 1 third of joins by about
> > 10%.
> > A quick scan of the "No"s seems to show quite a few cases which do not
> look
> > that real world like. e.g cartesian join.
>
> I don't have an idea how many queries in the reality hit this but
> I suppose it's not a few.
>
> > It would be great if someone could run some PostgreSQL application with
> > these 2 patches applied, and then grep the logs for the Unique Inner
> > results... Just to get a better idea of how many joins in a real world
> case
> > will benefit from this patch.
>
> Wow. I think the second patch should be DEBUGx, not NOTICE:)
>
>
I didn't give that much thought, but you're probably right. It was just a
quick test and demo to try to raise some more interest in this. :-)

Regards

David Rowley


unijoin_2015-03-28_d1923fb.patch
Description: Binary data

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