Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eWMeE-0001wg-ME for pgsql-performance@arkaria.postgresql.org; Tue, 02 Jan 2018 13:30:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1eWMeE-0008M9-9h for pgsql-performance@arkaria.postgresql.org; Tue, 02 Jan 2018 13:30:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1eWMeE-0008Lu-10 for pgsql-performance@lists.postgresql.org; Tue, 02 Jan 2018 13:30:30 +0000 Received: from mail-wr0-x22c.google.com ([2a00:1450:400c:c0c::22c]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1eWMe6-0000xr-9w for pgsql-performance@postgresql.org; Tue, 02 Jan 2018 13:30:29 +0000 Received: by mail-wr0-x22c.google.com with SMTP id o15so16328860wrf.12 for ; Tue, 02 Jan 2018 05:30:21 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=from:message-id:mime-version:subject:date:in-reply-to:cc:to :references; bh=4qNzHoel9TouRd1UkS6EYqnwxvQ7ziPfmZI96eq4Tmg=; b=ikqeoLgtRqLA4c3G+SSQb4J7x6fQvmJEnI5S1cFStVEygmpb5kUhYoNPdjFzbDjer4 Z36a43VUTiv/Cu06dQn8zbYGV5Jm59Gq2CvvajN9MObwBtfzlPrNkrMGKmMpecUsl7kQ FXME36K0YfUMgMF9ANh/DVC47yrfpHOTtcuVeTOsKrwCs1LXm7FePd1+nGOAgZAwwJ9q B63dw8WqHZ+Yuuu4HyUTsLKMoTV9e10nTB4Lkkrb4KF3e5pJx8dP8k5dNEF5pLfjeEaW uEMYnrN3sDonzlJrNvVt2V6eA0M97/HIpvCtXRIvlYw9HMBq35B1g2gSXgJnGFTg85Ny kO/w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:from:message-id:mime-version:subject:date :in-reply-to:cc:to:references; bh=4qNzHoel9TouRd1UkS6EYqnwxvQ7ziPfmZI96eq4Tmg=; b=njpTxwb8DA7emKn3S2dJjlTkyPyi+FO2S9X0cPs1cw5bX0x88uJyAk4LpyUBfbNtEd 7nx95niUQWDjgXRODlxVHAjMsQkcsm8HrDAbgnjamtu2gHLW8kFwe4vf57csbg22pfOY gcGAmP84lrnurpD7VB6Vobu39Z9r7D59Xarmo2ppgmNT2p7nZpW+nRYropJzI+Swg9FO jlVsqou0twvUP5aTlnvOKFNBRxICE7PpxL0xvKp12iyowxb6fHNM5LTr7wdi+fQfb+cO fyZ0h1MAP0EziL7d9ZuWJHYocBNQ3qEhjZe53QHMgl7TntoCxdTATBT6VXGLh/ZScUIh 0pvA== X-Gm-Message-State: AKGB3mLOltXmQ5YI8q6srHu7UHY+pM7HhLi1+ZKS8yQ/QeUU0vYKn49c sFccEj2gA40I6tJOPcc/vzA= X-Google-Smtp-Source: ACJfBouF76c/su99ltSgHuoXME4T4C2Sl85xmaGdG+VV7I/F9jF73knIeVYFZpGgdPPRp8R0sp6jIQ== X-Received: by 10.223.135.10 with SMTP id a10mr30037943wra.236.1514899819781; Tue, 02 Jan 2018 05:30:19 -0800 (PST) Received: from [192.168.1.5] (athedsl-4494727.home.otenet.gr. [94.71.129.143]) by smtp.gmail.com with ESMTPSA id p17sm26382161wma.23.2018.01.02.05.30.18 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Tue, 02 Jan 2018 05:30:19 -0800 (PST) From: Vasilis Ventirozos Message-Id: <39C0ED47-99F1-4433-9645-48F25739836C@gmail.com> Content-Type: multipart/alternative; boundary="Apple-Mail=_DAB1BAF9-238A-4079-8730-65EB03F317D5" Mime-Version: 1.0 (Mac OS X Mail 11.2 \(3445.5.20\)) Subject: Re: Restoring a table is ten times slower on Ubuntu 14.04 than on Ubuntu 16.04 Date: Tue, 2 Jan 2018 15:30:17 +0200 In-Reply-To: Cc: "pgsql-performance@postgresql.org" To: Hans Braxmeier References: X-Mailer: Apple Mail (2.3445.5.20) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --Apple-Mail=_DAB1BAF9-238A-4079-8730-65EB03F317D5 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii 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 = wrote: >=20 > Hi, >=20 > 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: >=20 > testdb=3D# \d photos_searchlog >=20 > Table "public.photos_searchlog" > Column | Type | Collation | Nullable | = Default =20 > = ----------+--------------------------+-----------+----------+-------------= --------------------------------- > id | integer | | not null | = nextval('photos_searchlog_id_seq'::regclass) > created | timestamp with time zone | | not null |=20 > updated | timestamp with time zone | | not null |=20 > lang | character varying(2) | | not null |=20 > q | character varying(255) | | not null |=20 > hits | integer | | not null |=20 > count | integer | | not null |=20 > ip_list | text | | not null |=20 > locked | boolean | | not null |=20 > ts_list | text | | not null |=20 > ts_count | integer | | not null |=20 > 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) >=20 > 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. >=20 > Does anybody have a clue what could cause the time difference? >=20 > Thanks --Apple-Mail=_DAB1BAF9-238A-4079-8730-65EB03F317D5 Content-Transfer-Encoding: quoted-printable Content-Type: text/html; charset=us-ascii 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.braxmeier@outlook.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=3D# \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

= --Apple-Mail=_DAB1BAF9-238A-4079-8730-65EB03F317D5--