Re: Table with large number of int columns, very slow COPY FROM

On 08.12.2017 05:21, Alex Tokarev wrote: I have made a minimally reproducible test case consisting of a table with 848 columns Such a high number of columns is maybe a sign of a wrong table / database design, why do you have such a lot of columns? How many indexes do you have? Regards, An

Re: Setting effective_io_concurrency in VM?

On 28/11/17 07:40, Scott Marlowe wrote: On Mon, Nov 27, 2017 at 11:23 AM, Don Seiler wrote: Good afternoon. We run Postgres (currently 9.2, upgrading to 9.6 shortly) in VMWare ESX machines. We currently have effective_io_concurrency set to the default of 1. I'm told that the data volume is a

Table with large number of int columns, very slow COPY FROM

Hi, I have a set of tables with fairly large number of columns, mostly int with a few bigints and short char/varchar columns. I¹ve noticed that Postgres is pretty slow at inserting data in such a table. I tried to tune every possible setting: using unlogged tables, increased shared_buffers, etc; e

Learning EXPLAIN

Hi experts! I read this nice article about Understanding EXPLAIN [1] weeks ago that opened my mind about the tool, but it seems no enough to explain a lot of plans that I see in this list. I often read responses to a plan that are not covered by the article. I need/want to know EXPLAIN better.

Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade

On Thu, Dec 7, 2017 at 2:31 PM, Laurenz Albe wrote: > Gunther wrote: >> Something is wrong with the dump thing. And no, it's not SSL or whatever, >> I am doing it on a local system with local connections. Version 9.5 >> something. > > That's a lot of useful information. > > Try to profile where t

Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade

Gunther wrote: > Something is wrong with the dump thing. And no, it's not SSL or whatever, > I am doing it on a local system with local connections. Version 9.5 something. That's a lot of useful information. Try to profile where the time is spent, using "perf" or similar. Do you connect via the

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= writes: > 2017-12-07 17:01 GMT+01:00 Tom Lane : >> It looks like the first time such a question is asked within a session, >> we build and cache a list of all the roles the session user is a member >> of (directly or indirectly). That's what's taking the time here -

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

2017-12-07 17:01 GMT+01:00 Tom Lane : > =?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= writes: > > I could reproduce part of the things I described earlier in this thread. > A > > guy named Andriy Senyshyn mailed me after reading this thread here (he > > could somehow not join the mailing list) and observed a di

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

=?UTF-8?Q?Ulf_Lohbr=C3=BCgge?= writes: > I could reproduce part of the things I described earlier in this thread. A > guy named Andriy Senyshyn mailed me after reading this thread here (he > could somehow not join the mailing list) and observed a difference when > issuing "SET ROLE" as user postgr

Re: [PERFORM] Slow execution of SET ROLE, SET search_path and RESET ROLE

I could reproduce part of the things I described earlier in this thread. A guy named Andriy Senyshyn mailed me after reading this thread here (he could somehow not join the mailing list) and observed a difference when issuing "SET ROLE" as user postgres and as a non-superuser. When I connect as su

Re: pg_dump 3 times as slow after 8.4 -> 9.5 upgrade

I confess I don't do dump or any backups much other than file system snapshots. But when I do, I don't like how long it takes. I confess my database is big, I have about 200 GB. But still, dumping it should not take 48 hours (and running) while the system is 75% idle and reads are at 4.5 MB/s