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

[Hans Braxmeier]

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

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

[Vasilis Ventirozos]

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:
> 
> 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

primary key hash index

[Rick Otten]

After reading this article about keys in relational databases, highlighted
on hacker news this morning:
https://begriffs.com/posts/2018-01-01-sql-keys-in-depth.html

I keep pondering the performance chart, regarding uuid insert, shown
towards the bottom of the article.  I believe he was doing that test with
PostgreSQL.

My understanding is that the performance is degrading because he has a
btree primary key index.  Is it possible to try a hash index or some other
index type for a uuid primary key that would mitigate the performance issue
he is recording?

After all, I can't think of any use case where I query for a "range" of
uuid values.  They are always exact matches.  So a hash index would
possibly be a really good fit.

I have many tables, several with more than 1 billion rows, that use uuid's
as the primary key.  Many of those uuid's are generated off system, so I
can't play around with the uuid generation algorithm like he was doing.

I'm hoping to move to PG 10 any day now, and can migrate the data with
updated index definitions if it will actually help performance in any way.
(I'm always looking for ways to tweak the performance for the better any
chance I get.)

Re: primary key hash index

[Magnus Hagander]

On Tue, Jan 2, 2018 at 3:02 PM, Rick Otten  wrote:

> After reading this article about keys in relational databases, highlighted
> on hacker news this morning:
> https://begriffs.com/posts/2018-01-01-sql-keys-in-depth.html
>
> I keep pondering the performance chart, regarding uuid insert, shown
> towards the bottom of the article.  I believe he was doing that test with
> PostgreSQL.
>
> My understanding is that the performance is degrading because he has a
> btree primary key index.  Is it possible to try a hash index or some other
> index type for a uuid primary key that would mitigate the performance issue
> he is recording?
>
> After all, I can't think of any use case where I query for a "range" of
> uuid values.  They are always exact matches.  So a hash index would
> possibly be a really good fit.
>
> I have many tables, several with more than 1 billion rows, that use uuid's
> as the primary key.  Many of those uuid's are generated off system, so I
> can't play around with the uuid generation algorithm like he was doing.
>
> I'm hoping to move to PG 10 any day now, and can migrate the data with
> updated index definitions if it will actually help performance in any way.
> (I'm always looking for ways to tweak the performance for the better any
> chance I get.)
>
>
Hash indexes unfortunately don't support UNIQUE indexes. At least not yet.
So while you can use them for regular indexing, they cannot be used as a
PRIMARY KEY.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/