Re: [GENERAL] vacuum of empty table slows down as database table count grows

Thanks, i'll redo the benchmarks and report back how things look now. 2017-01-04 20:33 GMT+01:00 Pavel Stehule : > >>> > >>> > This is irrelevant of amount of data restored, i am seeing the same >>> behavior with just schema restore, as well as with schema+data restores.

Re: [GENERAL] The best way to deal with hierarchical data (was: Postgresql query HAVING do not work)

On 1/4/17, Gwork wrote: > On 1/5/17 2:51 AM, Vitaly Burovoy wrote: >> On 1/4/17, Gwork wrote: >>> On 1/5/17 2:22 AM, Vitaly Burovoy wrote: On 1/4/17, Vitaly Burovoy wrote: > On 1/4/17, Gwork wrote: >>

Re: [GENERAL] [BUGS] Postgresql query HAVING do not work

On 1/4/17, Gwork wrote: > On 1/5/17 2:22 AM, Vitaly Burovoy wrote: >> On 1/4/17, Vitaly Burovoy wrote: >>> On 1/4/17, Gwork wrote: Version: Postgresql 9.5 OS: Debian 8 jessie run on docker Following this tutorial

Re: [GENERAL] Performance degradation when using auto_explain

On 01/04/2017 08:54 PM, Kisung Kim wrote: On Wed, Jan 4, 2017 at 1:21 AM, Andreas Kretschmer > wrote: Kisung Kim > wrote: > And finally I found that auto_explain is the cause of

Re: [GENERAL] Index impact on update?

On 01/04/2017 05:59 PM, Israel Brewster wrote: Short version: Do indexes impact the speed of an UPDATE, even when the indexed columns aren't changing? They shouldn't, as long as the updated tuple can be updated on the same page (8kB chunk of data). In that case we can do a HOT update for the

[GENERAL] Re: could not load library "$libdir/sslutils": in pg_upgrade process

Adrian, Tom Finally I did upgrade version but I've removed database pem (Postgres Enterprise Manager) I guess that this database has some link in some function to sslutils, because pg_upgrade showed the above errors while upgraded this database. /pg_restore: creating FUNCTION

Re: [GENERAL] Performance PLV8 vs PLPGSQL

On Wed, Dec 28, 2016 at 3:15 AM, Tim Uckun wrote: > I have seen various links on the internet which indicate that PLV8 is > significantly faster than PL-PGSQL sometimes an order of magnitude faster. > > Is this uniformly true or is it just in certain circumstances? > > Is

Re: [GENERAL] vacuum of empty table slows down as database table count grows

2017-01-04 20:22 GMT+01:00 Jerry Sievers : > marcin kowalski writes: > > > I am experiencing an odd issue, i've noticed it on 9.3 , but i can > reproduce it on 9.6. > > > > Basically, i have a database with a lot of schemas, but not that much > data.

Re: [GENERAL] vacuum of empty table slows down as database table count grows

marcin kowalski writes: > I am experiencing an odd issue, i've noticed it on 9.3 , but i can reproduce > it on 9.6. > > Basically, i have a database with a lot of schemas, but not that much data. > Each schema is maybe 2-4 GB in size, and often much less than that. > > The

Re: [GENERAL] Re: could not load library "$libdir/sslutils": in pg_upgrade process

On 01/04/2017 09:27 AM, DrakoRod wrote: Teorycally, I removed the sslutils from old cluster when review the $libdir appear this: What where the exact steps you took to remove sslutils? /[postgres@server ~]$ /opt/PostgreSQL/9.3/bin/pg_config --pkglibdir /opt/PostgreSQL/9.3/lib/postgresql

Re: [GENERAL] Index impact on update?

> On Jan 4, 2017, at 8:08 AM, Paul Ramsey wrote: > > You'd be better off forcing the table to write in bulk with something like > > CREATE TABLE mynewtable AS > SELECT *, geography(ST_SetSRID(ST_MakePoint(lng, lat), 4326)) AS geog > FROM myoldtable; > > Then index

Re: [GENERAL] replication slot to be used in the future

2017-01-04 18:24 GMT+01:00 Adrian Klaver : > On 01/04/2017 08:44 AM, Tom DalPozzo wrote: > >> Hi, >> > > Postgres version? > > Because in 9.6: > > https://www.postgresql.org/docs/9.6/static/functions-admin.h > tml#FUNCTIONS-REPLICATION > > Table 9-82. Replication SQL

[GENERAL] Re: could not load library "$libdir/sslutils": in pg_upgrade process

Teorycally, I removed the sslutils from old cluster when review the $libdir appear this: /[postgres@server ~]$ /opt/PostgreSQL/9.3/bin/pg_config --pkglibdir /opt/PostgreSQL/9.3/lib/postgresql [postgres@server ~]$ /opt/PostgreSQL/9.3/bin/pg_config --libs -lpgport -lpgcommon -lxslt -lxml2 -lpam

Re: [GENERAL] replication slot to be used in the future

On 01/04/2017 08:44 AM, Tom DalPozzo wrote: Hi, Postgres version? Because in 9.6: https://www.postgresql.org/docs/9.6/static/functions-admin.html#FUNCTIONS-REPLICATION Table 9-82. Replication SQL Functions pg_create_physical_replication_slot(slot_name name [, immediately_reserve boolean

Re: [GENERAL] Index impact on update?

On Jan 4, 2017, at 8:08 AM, Paul Ramsey wrote: > > You'd be better off forcing the table to write in bulk with something like > > CREATE TABLE mynewtable AS > SELECT *, geography(ST_SetSRID(ST_MakePoint(lng, lat), 4326)) AS geog > FROM myoldtable; > > Then index the

Re: [GENERAL] Index impact on update?

On 01/04/2017 09:59 AM, Israel Brewster wrote: Short version: Do indexes impact the speed of an UPDATE, even when the indexed columns aren't changing? Details: I have a table containing geographical data (Latitude, longitude, and elevation) with 406,833,705 records. The Latitude and

Re: [GENERAL] Index impact on update?

On 01/04/2017 09:59 AM, Israel Brewster wrote: Short version: Do indexes impact the speed of an UPDATE, even when the indexed columns aren't changing? Details: I have a table containing geographical data (Latitude, longitude, and elevation) with 406,833,705 records. The Latitude and

Re: [GENERAL] Index impact on update?

You'd be better off forcing the table to write in bulk with something like CREATE TABLE mynewtable AS SELECT *, geography(ST_SetSRID(ST_MakePoint(lng, lat), 4326)) AS geog FROM myoldtable; Then index the new table, rename, etc. Bulk update will, in addition to being slow, use 2x the amount of

[GENERAL] Index impact on update?

Short version:Do indexes impact the speed of an UPDATE, even when the indexed columns aren't changing?Details:I have a table containing geographical data (Latitude, longitude, and elevation) with 406,833,705 records. The Latitude and Longitude columns are indexed. In order to better utilize the

[GENERAL] replication slot to be used in the future

Hi, I've got my primary and I make a pg_basebackup -x in order to create a standby. I can connect my standby only later, in some hours, so I'd like the master to keep new WALs but I don't like to use archiving nor keep-segments option. I thought to do it through a physical replication slot (my

Re: [GENERAL] COPY: row is too big

On 01/04/2017 08:32 AM, Steve Crawford wrote: ... Numeric is expensive type - try to use float instead, maybe double. If I am following the OP correctly the table itself has all the columns declared as varchar. The data in the CSV file is a mix of text, date and numeric,

Re: [GENERAL] COPY: row is too big

... > Numeric is expensive type - try to use float instead, maybe double. >> > > If I am following the OP correctly the table itself has all the columns > declared as varchar. The data in the CSV file is a mix of text, date and > numeric, presumably cast to text on entry into the table. > But a

Re: [GENERAL] COPY: row is too big

On 01/04/2017 08:00 AM, rob stone wrote: Hello, On Wed, 2017-01-04 at 07:11 -0800, Adrian Klaver wrote: On 01/04/2017 06:54 AM, Pavel Stehule wrote: Hi 2017-01-04 14:00 GMT+01:00 vod vos >: __ Now I am confused about I can create 1100 columns

Re: [GENERAL] COPY: row is too big

On 2017-01-04 06:53:31 -0800, Adrian Klaver wrote: > On 01/04/2017 05:00 AM, vod vos wrote: > >Now I am confused about I can create 1100 columns in a table in > >postgresql, but I can't copy 1100 values into the table. And I really > > As pointed out previously: > >

Re: [GENERAL] COPY: row is too big

Hello, On Wed, 2017-01-04 at 07:11 -0800, Adrian Klaver wrote: > On 01/04/2017 06:54 AM, Pavel Stehule wrote: > > Hi > > > > 2017-01-04 14:00 GMT+01:00 vod vos > >: > > > > __ > > Now I am confused about I can create 1100 columns in a table in >

Fwd: [GENERAL] Cannot recover from backup on barman

Sorry this message got out of the list. -- Forwarded message -- From: Alfredo Palhares Date: Wed, Jan 4, 2017 at 11:59 AM Subject: Re: [GENERAL] Cannot recover from backup on barman To: Michael Paquier Hello Michael, I don't

Re: [GENERAL] COPY: row is too big

2017-01-04 16:11 GMT+01:00 Adrian Klaver : > On 01/04/2017 06:54 AM, Pavel Stehule wrote: > >> Hi >> >> 2017-01-04 14:00 GMT+01:00 vod vos > >: >> >> __ >> Now I am confused about I can create 1100 columns in a table in

Re: [GENERAL] COPY: row is too big

OK, maybe the final solution is to split it into half. On 星期三, 04 一月 2017 06:53:31 -0800 Adrian Klaver adrian.kla...@aklaver.com wrote On 01/04/2017 05:00 AM, vod vos wrote: Now I am confused about I can create 1100 columns in a table in postgresql, but I can't copy 1100

Re: [GENERAL] Re: could not load library "$libdir/sslutils": in pg_upgrade process

On 01/03/2017 09:01 PM, DrakoRod wrote: Yes I installed Postgres Enterprise Manager Agent time ago in this server to test agent, but now I don't use it. Amm if you refer the EDB install with binaries PostgreSQL one-click yes, but is not a EDB Advanced Server , is a normal Cluster installed by

Re: [GENERAL] COPY: row is too big

On 01/04/2017 06:54 AM, Pavel Stehule wrote: Hi 2017-01-04 14:00 GMT+01:00 vod vos >: __ Now I am confused about I can create 1100 columns in a table in postgresql, but I can't copy 1100 values into the table. And I really dont want to

[GENERAL] vacuum of empty table slows down as database table count grows

I am experiencing an odd issue, i've noticed it on 9.3 , but i can reproduce it on 9.6. Basically, i have a database with a lot of schemas, but not that much data. Each schema is maybe 2-4 GB in size, and often much less than that. The database has ~300-500 schemas, each with ~100-300 tables.

Re: [GENERAL] COPY: row is too big

Hi 2017-01-04 14:00 GMT+01:00 vod vos : > Now I am confused about I can create 1100 columns in a table in > postgresql, but I can't copy 1100 values into the table. And I really dont > want to split the csv file to pieces to avoid mistakes after this action. > The PostgreSQL

Re: [GENERAL] COPY: row is too big

On 01/04/2017 05:00 AM, vod vos wrote: Now I am confused about I can create 1100 columns in a table in postgresql, but I can't copy 1100 values into the table. And I really As pointed out previously: https://www.postgresql.org/about/ Maximum Columns per Table 250 - 1600 depending on

Re: [GENERAL] Row value expression much faster than equivalent OR clauses

On Wed, Dec 28, 2016 at 10:58 AM, Steven Grimm wrote: > WHERE ((e.timeStamp > '2016-12-19T20:34:22.315Z') > OR (e.timeStamp = '2016-12-19T20:34:22.315Z' > AND e.sequenceNumber > 0) > OR (e.timeStamp = '2016-12-19T20:34:22.315Z' > AND

Re: [GENERAL] COPY: row is too big

Now I am confused about I can create 1100 columns in a table in postgresql, but I can't copy 1100 values into the table. And I really dont want to split the csv file to pieces to avoid mistakes after this action. I create a table with 1100 columns with data type of varchar, and hope the COPY

Re: [GENERAL] Performance degradation when using auto_explain

Kisung Kim wrote: > And finally I found that auto_explain is the cause of the problem. real hardware or virtual hardware? On virtual there are sometimes problems with exact timings, please read: https://www.postgresql.org/docs/current/static/pgtesttiming.html Regards,