pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRestoring a table is ten times slower on Ubuntu 14.04 than on Ubuntu 16.04
2+ messages / 2 participants
[nested] [flat]
* Restoring a table is ten times slower on Ubuntu 14.04 than on Ubuntu 16.04
@ 2018-01-02 13:27 Hans Braxmeier <hans.braxmeier@outlook.com>
2018-01-02 13:30 ` Re: Restoring a table is ten times slower on Ubuntu 14.04 than on Ubuntu 16.04 Vasilis Ventirozos <v.ventirozos@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Hans Braxmeier @ 2018-01-02 13:27 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Restoring a table is ten times slower on Ubuntu 14.04 than on Ubuntu 16.04
2018-01-02 13:27 Restoring a table is ten times slower on Ubuntu 14.04 than on Ubuntu 16.04 Hans Braxmeier <hans.braxmeier@outlook.com>
@ 2018-01-02 13:30 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Vasilis Ventirozos @ 2018-01-02 13:30 UTC (permalink / raw)
To: Hans Braxmeier <hans.braxmeier@outlook.com>; +Cc: pgsql-performance
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=# \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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2018-01-02 13:30 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-01-02 13:27 Restoring a table is ten times slower on Ubuntu 14.04 than on Ubuntu 16.04 Hans Braxmeier <hans.braxmeier@outlook.com>
2018-01-02 13:30 ` Vasilis Ventirozos <v.ventirozos@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox