Restoring a table is ten times slower on Ubuntu 14.04 than on Ubuntu 16.04

From: Hans Braxmeier <hans(dot)braxmeier(at)outlook(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Restoring a table is ten times slower on Ubuntu 14.04 than on Ubuntu 16.04
Date: 2018-01-02 13:27:29
Message-ID: VI1PR0402MB393624C592AB5106DC92A92499190@VI1PR0402MB3936.eurprd04.prod.outlook.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I try to restore a table on U16.04, but it's ten times slower than on U14.04. This is the definition of the table:

testdb=# \d photos_searchlog

Table "public.photos_searchlog"
Column | Type | Collation | Nullable | Default
----------+--------------------------+-----------+----------+----------------------------------------------
id | integer | | not null | nextval('photos_searchlog_id_seq'::regclass)
created | timestamp with time zone | | not null |
updated | timestamp with time zone | | not null |
lang | character varying(2) | | not null |
q | character varying(255) | | not null |
hits | integer | | not null |
count | integer | | not null |
ip_list | text | | not null |
locked | boolean | | not null |
ts_list | text | | not null |
ts_count | integer | | not null |
Indexes:
"photos_searchlog_pkey" PRIMARY KEY, btree (id)
"photos_searchlog_lang_q_key" UNIQUE CONSTRAINT, btree (lang, q)
"photos_searchlog_count" btree (count)
"photos_searchlog_created" btree (created)
"photos_searchlog_ts_count" btree (ts_count)
"photos_searchlog_updated" btree (updated)

It's only the statement ALTER TABLE ONLY photos_searchlog ADD CONSTRAINT photos_searchlog_lang_q_key UNIQUE (lang, q); which causes the delay. I use the default postgres configuration on the same hardware (/etc/postgresql/10/main/postgresql.conf). I tested different postgres versions, checked the locale and other settings but can not find any differences. I also tried with more or less data, but always the same result.

Does anybody have a clue what could cause the time difference?

Thanks

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Vasilis Ventirozos 2018-01-02 13:30:17 Re: Restoring a table is ten times slower on Ubuntu 14.04 than on Ubuntu 16.04
Previous Message Justin Pryzby 2017-12-30 18:42:47 Re: partitioning an existing table - efficient pg_dump