Postgres very slow on server

Question

Restoring a single table on the server is very slow

From Fri Jul 22 13:54:48 CEST 2016 to Fri Jul 22 14:17:41 CEST 2016
Server
2016-07-22 13:55:11 CEST [88719-1] tester@test LOG:  duration: 22988.122 ms  statement: COPY testtable (id, updated, lang, q, results) FROM stdin;
2016-07-22 14:06:09 CEST [88719-2] tester@test LOG:  duration: 658189.147 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
2016-07-22 14:06:10 CEST [88719-3] tester@test LOG:  duration: 1546.800 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_pkey PRIMARY KEY (id);
2016-07-22 14:06:13 CEST [88719-4] tester@test LOG:  duration: 2305.070 ms  statement: CREATE INDEX testtable_lang ON testtable USING btree (lang);
2016-07-22 14:06:15 CEST [88719-5] tester@test LOG:  duration: 2374.251 ms  statement: CREATE INDEX testtable_lang_like ON testtable USING btree (lang varchar_pattern_ops);
2016-07-22 14:17:38 CEST [88719-6] tester@test LOG:  duration: 683316.708 ms  statement: CREATE INDEX testtable_q ON testtable USING btree (q);
2016-07-22 14:17:41 CEST [88719-7] tester@test LOG:  duration: 2839.739 ms  statement: CREATE INDEX testtable_q_like ON testtable USING btree (q varchar_pattern_ops);

Local machine

From Fr 22. Jul 14:38:54 CEST 2016 to Fr 22. Jul 14:42:05 CEST 2016
Lokal
2016-07-22 14:39:32 CEST [23005-1] tester@test LOG:  duration: 37444.240 ms  statement: COPY testtable (id, updated, lang, q, results) FROM stdin;
2016-07-22 14:40:44 CEST [23005-2] tester@test LOG:  duration: 71899.607 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
2016-07-22 14:40:46 CEST [23005-3] tester@test LOG:  duration: 2027.237 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_pkey PRIMARY KEY (id);
2016-07-22 14:40:49 CEST [23005-4] tester@test LOG:  duration: 3781.663 ms  statement: CREATE INDEX testtable_lang ON testtable USING btree (lang);
2016-07-22 14:40:52 CEST [23005-5] tester@test LOG:  duration: 2731.623 ms  statement: CREATE INDEX testtable_lang_like ON testtable USING btree (lang varchar_pattern_ops);
2016-07-22 14:42:02 CEST [23005-6] tester@test LOG:  duration: 69512.264 ms  statement: CREATE INDEX testtable_q ON testtable USING btree (q);
2016-07-22 14:42:05 CEST [23005-7] tester@test LOG:  duration: 3177.827 ms  statement: CREATE INDEX testtable_q_like ON testtable USING btree (q varchar_pattern_ops);

testtable

                                  Table "public.testtable"
 Column  |           Type           |                              Modifiers                              
---------+--------------------------+---------------------------------------------------------------------
 id      | integer                  | not null default nextval('testtable_id_seq'::regclass)
 updated | timestamp with time zone | not null
 lang    | character varying(2)     | not null
 q       | character varying(100)   | not null
 results | text                     | not null
Indexes:
    "testtable_pkey" PRIMARY KEY, btree (id)
    "testtable_lang_q_key" UNIQUE CONSTRAINT, btree (lang, q)
    "testtable_lang" btree (lang)
    "testtable_lang_like" btree (lang varchar_pattern_ops)
    "testtable_q" btree (q)
    "testtable_q_like" btree (q varchar_pattern_ops)

Tested with PG 9.4 and 9.5 on both machines with the same configuration. Restoring the dump takes even much longer on the server if auto vacuum is enabled...

What could be the problem? What's going wrong here? Any ideas how to debug?

Update: U 16.04 is running on server, U 14.04 LTS on local machine. Number of rows: 1574775, Size of plain SQL dump 1.8 GB.

score 0 · Answer 1 · edited Apr 13 '17 at 12:42

Looking at your server times: 23 seconds to restore a table with 1,6 million rows from a 1.8 GB dump file. That does not seem too bad. Might be faster, but doesn't seem very slow either.

To speed up index creation (as well as UNIQUE and PK constraints) set maintenance_work_mem high enough temporarily (for the local session or transaction only). The manual:

maintenance_work_mem (integer)

... Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. It defaults to 64 megabytes (64MB).

Set it just high enough for Postgres to process everything in RAM (If you have enough RAM to spare (temporarily). For instance:

BEGIN;
SET LOCAL maintenance_work_mem = 1GB;  -- adapt to your needs
ALTER TABLE testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
COMMIT;

The manual warns:

Note that when autovacuum runs, up to autovacuum_max_workers times this memory may be allocated, so be careful not to set the default value too high.

Setting it high for your transaction only avoids this problem.

Is a clustered index on column A the same as creating a table ordered by A?

Postgres very slow on server

1 Answers1