Re: [HACKERS] More stable query plans via more predictable column statistics

2016-01-23 Thread Tomas Vondra
Hi, On 01/20/2016 10:49 PM, Alvaro Herrera wrote: Tom Lane wrote: "Shulgin, Oleksandr" writes: This post summarizes a few weeks of research of ANALYZE statistics distribution on one of our bigger production databases with some real-world data and proposes a

Re: [HACKERS] insert/update performance

2016-01-23 Thread Amit Kapila
On Sat, Jan 23, 2016 at 1:16 PM, Jinhua Luo wrote: > > Hi, > > The vacuum doesn't recycle the rows obsoleted by update? > It does free up the space which can be used by future inserts. > I don't think > so. In the above vacuum result, I do not delete any rows, but the >

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

2016-01-23 Thread David Rowley
On 23 January 2016 at 12:44, Tom Lane wrote: > I spent some time looking through this but decided that it needs some work > to be committable. > > The main thing I didn't like was that identify_key_vars seems to have a > rather poorly chosen API: it mixes up identifying a

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

2016-01-23 Thread David Rowley
On 24 January 2016 at 00:56, Julien Rouhaud wrote: > I wonder if in remove_useless_groupby_columns(), in the foreach loop you > could change the > > + if (bms_subset_compare(pkattnos, relattnos) == BMS_SUBSET1) > + { > > by something like > > > + if

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

2016-01-23 Thread Julien Rouhaud
On 23/01/2016 10:44, David Rowley wrote: > On 23 January 2016 at 12:44, Tom Lane wrote: >> I spent some time looking through this but decided that it needs some work >> to be committable. >> >> The main thing I didn't like was that identify_key_vars seems to have a >> rather

Re: [HACKERS] insert/update performance

2016-01-23 Thread Jinhua Luo
Hi, 2016-01-23 18:40 GMT+08:00 Amit Kapila : > At the moment, I am also not able to see why it is so. You might > want to first try with a simple test (Can you extract insert/update > statements from application and run it manually for couple of times > and then run

Re: [HACKERS] pgbench stats per script & other stuff

2016-01-23 Thread Fabien COELHO
Hello Alvaro, I'm looking at this part of your patch and I think it's far too big to be a simple refactoring. Would you split it up please? You know how delighted I am to split patches... Here is a 5 part ordered patch serie: a) add -b option for cumulating builtins and rework internal

Re: [HACKERS] Relation extension scalability

2016-01-23 Thread Amit Kapila
On Sat, Jan 23, 2016 at 12:19 PM, Amit Kapila wrote: > On Tue, Jan 12, 2016 at 2:41 PM, Dilip Kumar > wrote: > >> On Thu, Jan 7, 2016 at 4:53 PM, Andres Freund wrote: >> >>> On 2016-01-07 16:48:53 +0530, Amit Kapila wrote: >>>

Re: [HACKERS] PoC: Partial sort

2016-01-23 Thread Tomas Vondra
Hi, On 10/20/2015 01:17 PM, Alexander Korotkov wrote: On Fri, Oct 16, 2015 at 7:11 PM, Alexander Korotkov > wrote: On Sun, Jun 7, 2015 at 11:01 PM, Peter Geoghegan > wrote: On Sun, Jun

Re: [HACKERS] Improved tab completion for FDW DDL

2016-01-23 Thread Peter Eisentraut
On 1/18/16 8:36 PM, Andreas Karlsson wrote: > On 01/11/2016 02:39 AM, Peter Eisentraut wrote: >> The second part is not necessary, because there is already code that >> completes FDW names after "FOREIGN DATA WRAPPER". So this already works. > > Good spot, thanks. I have no idea why I did not

Re: [HACKERS] Proposal: Trigonometric functions in degrees

2016-01-23 Thread Tom Lane
Noah Misch writes: > On Sat, Jan 23, 2016 at 12:08:40PM -0500, Tom Lane wrote: >> So I pushed that, and tern/sungazer are still failing. Noah, could you >> trace through that and see exactly where it's going off the rails? > The second sin() is a constant, so gcc computes it

Re: [HACKERS] insert/update performance

2016-01-23 Thread Tom Lane
Jinhua Luo writes: > I have a table with 70 columns, and 6 indexes. The data flow is a > special OLTP model: frequent inserts (2000 tps), and each inserted row > would be updated very soon (i.e. the number of inserts is equal to the > number of updates). Do those predictable

Re: [HACKERS] Proposal: Trigonometric functions in degrees

2016-01-23 Thread Noah Misch
On Sat, Jan 23, 2016 at 12:08:40PM -0500, Tom Lane wrote: > I wrote: > > So the early returns from the buildfarm aren't very good: > > * tern/sungazer isn't getting exactly 0.5 from sind(30). > > > The tern/sungazer result implies that this: > > > return (sin(x * (M_PI / 180.0)) / sin(30.0 *

Re: [HACKERS] silent data loss with ext4 / all current versions

2016-01-23 Thread Michael Paquier
On Sat, Jan 23, 2016 at 11:39 AM, Tomas Vondra wrote: > On 01/23/2016 02:35 AM, Michael Paquier wrote: >> >> On Fri, Jan 22, 2016 at 9:41 PM, Greg Stark wrote: >>> On Fri, Jan 22, 2016 at 8:26 AM, Tomas Vondra >>> wrote:

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

2016-01-23 Thread Julien Rouhaud
On 23/01/2016 14:51, David Rowley wrote: > On 24 January 2016 at 00:56, Julien Rouhaud wrote: >> I wonder if in remove_useless_groupby_columns(), in the foreach loop you >> could change the >> >> + if (bms_subset_compare(pkattnos, relattnos) == BMS_SUBSET1) >> +

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

2016-01-23 Thread Tom Lane
David Rowley writes: > On 23 January 2016 at 12:44, Tom Lane wrote: >> * What you did to join.sql/join.out seems a bit bizarre. The existing >> test case that you modified was meant to test something else, and >> conflating this behavior with

Re: [HACKERS] Proposal: Trigonometric functions in degrees

2016-01-23 Thread Tom Lane
Peter Eisentraut writes: > On 1/23/16 12:08 PM, Tom Lane wrote: >> So I pushed that, and tern/sungazer are still failing. Noah, could you >> trace through that and see exactly where it's going off the rails? > I'm still getting a failure in float8 on OS X after commit 73193d8:

Re: [HACKERS] proposal: function parse_ident

2016-01-23 Thread Tom Lane
Michael Paquier writes: > On Sat, Jan 23, 2016 at 1:25 AM, Marko Tiikkaja wrote: > + errmsg("identifier contains disallowed chars"), > + errdetail("string \"%s\" is not valid identifier", > +

Re: [HACKERS] Proposal: Trigonometric functions in degrees

2016-01-23 Thread Tom Lane
I wrote: > So the early returns from the buildfarm aren't very good: > * tern/sungazer isn't getting exactly 0.5 from sind(30). > The tern/sungazer result implies that this: > return (sin(x * (M_PI / 180.0)) / sin(30.0 * (M_PI / 180.0))) / 2.0; > is not producing exactly 0.5, which means

Re: [HACKERS] Proposal: Trigonometric functions in degrees

2016-01-23 Thread Peter Eisentraut
On 1/23/16 12:08 PM, Tom Lane wrote: > So I pushed that, and tern/sungazer are still failing. Noah, could you > trace through that and see exactly where it's going off the rails? I'm still getting a failure in float8 on OS X after commit 73193d8: @@ -490,9 +490,9 @@ x | asind | acosd |

Re: [HACKERS] proposal: function parse_ident

2016-01-23 Thread Michael Paquier
On Sat, Jan 23, 2016 at 1:25 AM, Marko Tiikkaja wrote: > Hi Pavel, > > Sorry for the lack of review here. I didn't realize I was still the > reviewer for this after it had been moved to another commitfest. > > That said, I think I've exhausted my usefulness here as a reviewer.

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

2016-01-23 Thread Tom Lane
Julien Rouhaud writes: > I wonder if in remove_useless_groupby_columns(), in the foreach loop you > could change the > + if (bms_subset_compare(pkattnos, relattnos) == BMS_SUBSET1) > + { > by something like > + if (bms_num_members(relattnos) <=

Re: [HACKERS] PoC: Partial sort

2016-01-23 Thread Peter Geoghegan
On Sat, Jan 23, 2016 at 4:07 AM, Tomas Vondra wrote: > The main thing I'm particularly interested in is how much is this coupled > with the Sort node, and whether it's possible to feed partially sorted > tuples into other nodes. That's cool, but I'm particularly

Re: [HACKERS] Proposal: Trigonometric functions in degrees

2016-01-23 Thread Peter Eisentraut
On 1/23/16 3:05 PM, Tom Lane wrote: > Peter Eisentraut writes: >> On 1/23/16 12:08 PM, Tom Lane wrote: >>> So I pushed that, and tern/sungazer are still failing. Noah, could you >>> trace through that and see exactly where it's going off the rails? > >> I'm still getting a

Re: [HACKERS] Proposal: Trigonometric functions in degrees

2016-01-23 Thread Tom Lane
Peter Eisentraut writes: > On 1/23/16 3:05 PM, Tom Lane wrote: >> Peter Eisentraut writes: >>> I'm still getting a failure in float8 on OS X after commit 73193d8: >> Weird, because my OS X critters are all happy. Which OS X and compiler >> version, exactly?

Re: [HACKERS] Combining Aggregates

2016-01-23 Thread Jeff Janes
On Fri, Jan 22, 2016 at 4:53 PM, David Rowley wrote: > > It seems that I must have mistakenly believed that non-existing > columns for previous versions were handled using the power of magic. > Turns out that I was wrong, and they need to be included as dummy >

Re: [HACKERS] Proposal: Trigonometric functions in degrees

2016-01-23 Thread Noah Misch
On Sat, Jan 23, 2016 at 05:04:56PM -0500, Tom Lane wrote: > Noah Misch writes: > > To reliably produce exact answers, this code must delay all trigonometric > > calculations to runtime. On sungazer, the float8 test happens to pass if I > > rebuild float.c with

Re: [HACKERS] Proposal: Trigonometric functions in degrees

2016-01-23 Thread Tom Lane
Noah Misch writes: > To reliably produce exact answers, this code must delay all trigonometric > calculations to runtime. On sungazer, the float8 test happens to pass if I > rebuild float.c with -fno-builtin-sin; that leaves calls like acos(0.5) and > cos(60.0 *

Re: [HACKERS] Patch: ResourceOwner optimization for tables with many partitions

2016-01-23 Thread Tom Lane
Aleksander Alekseev writes: > Oops, wrong patches - here are correct ones. This is certainly not doing what I had in mind for communication between ResourceOwnerGetAny and ResourceOwnerRelease, because the latter pays zero attention to "lastidx", but instead does a

Re: [HACKERS] pg_dump fails on domain constraint comments

2016-01-23 Thread Elvis Pranskevichus
On January 22, 2016 08:09:36 PM Alvaro Herrera wrote: > Michael Paquier wrote: > > On Tue, Jan 12, 2016 at 7:56 AM, Elvis Pranskevichus wrote: > > > It looks like pg_dump emits incorrect text for domain constraint > > > comments: > > > > > > Assuming the following structure,

Re: [HACKERS] Proposal: Trigonometric functions in degrees

2016-01-23 Thread Tom Lane
Noah Misch writes: > On Sat, Jan 23, 2016 at 05:04:56PM -0500, Tom Lane wrote: >> Either I missed something or there's another issue, because tern/sungazer >> are *still* failing. This is getting annoying :-( > sungazer's "make check" passes if I change

Re: [HACKERS] insert/update performance

2016-01-23 Thread Jinhua Luo
And I also found the pg_stat_all_tables may be not so accurate. testdb=# truncate test; testdb=# select pg_stat_reset_single_table_counters(42515); testdb=# select n_tup_ins,n_tup_upd,n_tup_hot_upd,n_tup_del,n_live_tup,n_dead_tup from pg_stat_all_tables where relid=42515; n_tup_ins | n_tup_upd |

Re: [HACKERS] insert/update performance

2016-01-23 Thread Jinhua Luo
2016-01-23 23:00 GMT+08:00 Tom Lane : > Jinhua Luo writes: >> I have a table with 70 columns, and 6 indexes. The data flow is a >> special OLTP model: frequent inserts (2000 tps), and each inserted row >> would be updated very soon (i.e. the number of

[HACKERS] is there a deep unyielding reason to limit U&'' literals to ASCII?

2016-01-23 Thread Chapman Flack
I see in the documentation (and confirm in practice) that a Unicode character string literal U&'...' is only allowed to have s representing Unicode characters if the server encoding is, exactly and only, UTF8. Otherwise, it can still have s, but they can only be in the range \+01 to \+7f