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

You are not providing too much info, its unclear to me whats actually slow.
If you can, try loading the data first and then create the indexes / constraints. that should be faster.

> On 2 Jan 2018, at 15:27, Hans Braxmeier <hans(dot)braxmeier(at)outlook(dot)com> wrote:
>
> 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