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.
Answer :
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, andALTER 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.
Related: