Re: [HACKERS] Index-only scans with btree_gist
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
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
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
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
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
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?
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
