Received: from malur.postgresql.org ([2a02:16a8:dc51::56])
	by arkaria.postgresql.org with esmtps
 (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.89)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1fb5p2-0005wj-5Z
	for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Jul 2018 15:05:28 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <pgsql-bugs-owner+archive@lists.postgresql.org>)
	id 1fb5oz-0001oP-SW
	for pgsql-bugs@arkaria.postgresql.org; Thu, 05 Jul 2018 15:05:25 +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.89)
	(envelope-from <g.smolkin@postgrespro.ru>)
	id 1fb5oz-0001o1-Hj
	for pgsql-bugs@lists.postgresql.org; Thu, 05 Jul 2018 15:05:25 +0000
Received: from mail.postgrespro.ru ([93.174.131.138])
	by magus.postgresql.org with esmtp (Exim 4.89)
	(envelope-from <g.smolkin@postgrespro.ru>)
	id 1fb5ot-0004GA-U8
	for pgsql-bugs@postgresql.org; Thu, 05 Jul 2018 15:05:24 +0000
Received: from localhost (localhost [127.0.0.1])
	by mail.postgrespro.ru (Postfix) with ESMTP id 3A0B221C2AC4
	for <pgsql-bugs@postgresql.org>; Thu,  5 Jul 2018 18:05:18 +0300 (MSK)
X-Virus-Scanned: Debian amavisd-new at postgrespro.ru
X-Spam-Flag: NO
X-Spam-Score: 0
X-Spam-Level: 
X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[]
	autolearn=unavailable
Received: from [192.168.27.179] (gw.postgrespro.ru [93.174.131.141])
	(using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits))
	(Client did not present a certificate)
	by mail.postgrespro.ru (Postfix) with ESMTPSA id 11E4921C2AAC
	for <pgsql-bugs@postgresql.org>; Thu,  5 Jul 2018 18:05:18 +0300 (MSK)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru;
	s=mail; t=1530803118;
	bh=Tp92pDuCgL+SEdDEs4aNA7jAKdVqhZKzxV4EgjjAEWo=;
	h=To:From:Subject:Date;
	b=iHsxINXIJeGQG4XKhlFDw+wED6O5jUXC3pqBaiIB+bpsC2gSqehTOH3XP84k6DNrN
	 tIRpCuiErjOouDQH1bKJRpPFflcTyGO78zNq9VODGXW3pIuZ9obEXigVO+lvxQF6BG
	 5LhYo4tQaMPPy3BvK+X2QZ16zAcM7A6KpTFwapIc=
To: Pg Bugs <pgsql-bugs@postgresql.org>
From: Grigory Smolkin <g.smolkin@postgrespro.ru>
Subject: long analyze, libc bug and libicu
Message-ID: <dc49b0d2-d20d-adeb-ecdb-0ad25461386d@postgrespro.ru>
Date: Thu, 5 Jul 2018 18:05:17 +0300
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
 Thunderbird/45.8.0
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
List-Id: <pgsql-bugs.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-bugs@lists.postgresql.org>
List-Owner: <mailto:pgsql-bugs-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-bugs>
Precedence: bulk

Hello, everyone!

One of your clients with PostgreSQL 10.4 recently noticed that ANALYZE 
started to take unproportionally long amount of time. Backtrace:

#0  0x00007f244b68ca06 in strcoll_l () from /lib/x86_64-linux-gnu/libc.so.6
#1  0x00007f244e37802d in varstrfastcmp_locale (x=139794027137284, 
y=139794027967812, ssup=<optimized out>) at 
/build/postgresql-10-U6N320/postgresql-10-10.4/build/../src/backend/utils/adt/varlena.c:2185
#2  0x00007f244e0d6b15 in ApplySortComparator (isNull1=0 '\000', 
isNull2=0 '\000', ssup=0x7fffa958de80, datum2=<optimized out>, 
datum1=<optimized out>) at 
/build/postgresql-10-U6N320/postgresql-10-10.4/build/../src/include/utils/sortsupport.h:225
#3  compare_scalars (a=<optimized out>, b=<optimized out>, 
arg=0x7fffa958de70) at 
/build/postgresql-10-U6N320/postgresql-10-10.4/build/../src/backend/commands/analyze.c:2821
#4  0x00007f244e3e1023 in med3 (a=0x7f24578f6bd8 "\224__U$\177", 
b=0x7f24578f6c78 "\004ITU$\177", c=0x7f24578f6d18 "D\365`U$\177", 
cmp=0x7f244e0d6af0 <compare_scalars>, arg=0x7fffa958de70) at 
/build/postgresql-10-U6N320/postgresql-10-10.4/build/../src/port/qsort_arg.c:108
#5  0x00007f244e3e13ec in qsort_arg (a=a@entry=0x7f24578f6bd8, 
n=<optimized out>, n@entry=82, es=es@entry=16, 
cmp=cmp@entry=0x7f244e0d6af0 <compare_scalars>, 
arg=arg@entry=0x7fffa958de70) at 
/build/postgresql-10-U6N320/postgresql-10-10.4/build/../src/port/qsort_arg.c:157
#6  0x00007f244e3e1475 in qsort_arg (a=a@entry=0x7f24578f6248, 
n=<optimized out>, n@entry=235, es=es@entry=16, 
cmp=cmp@entry=0x7f244e0d6af0 <compare_scalars>, 
arg=arg@entry=0x7fffa958de70) at 
/build/postgresql-10-U6N320/postgresql-10-10.4/build/../src/port/qsort_arg.c:217
#7  0x00007f244e3e1475 in qsort_arg (a=a@entry=0x7f24578f5348, 
n=<optimized out>, es=es@entry=16, cmp=cmp@entry=0x7f244e0d6af0 
<compare_scalars>, arg=arg@entry=0x7fffa958de70) at 
/build/postgresql-10-U6N320/postgresql-10-10.4/build/../src/port/qsort_arg.c:217
#8  0x00007f244e3e13ac in qsort_arg (a=a@entry=0x7f24578f5348, 
n=<optimized out>, es=es@entry=16, cmp=cmp@entry=0x7f244e0d6af0 
<compare_scalars>, arg=arg@entry=0x7fffa958de70) at 
/build/postgresql-10-U6N320/postgresql-10-10.4/build/../src/port/qsort_arg.c:203
#9  0x00007f244e3e13ac in qsort_arg (a=0x7f24578f5348, 
a@entry=0x7f2457816928, n=<optimized out>, n@entry=109083, 
es=es@entry=16, cmp=cmp@entry=0x7f244e0d6af0 <compare_scalars>, 
arg=arg@entry=0x7fffa958de70) at 
/build/postgresql-10-U6N320/postgresql-10-10.4/build/../src/port/qsort_arg.c:203
#10 0x00007f244e0d604f in compute_scalar_stats (stats=0x7f244fd168f8, 
fetchfunc=<optimized out>, samplerows=110194, totalrows=110194) at 
/build/postgresql-10-U6N320/postgresql-10-10.4/build/../src/backend/commands/analyze.c:2378
#11 0x00007f244e0d76f9 in do_analyze_rel 
(onerel=onerel@entry=0x7f244fc339d8, options=options@entry=99, 
params=params@entry=0x7f244f98b9f0, va_cols=va_cols@entry=0x0, 
acquirefunc=<optimized out>, relpages=11515, inh=0 '\000', 
in_outer_xact=0 '\000', elevel=13) at 
/build/postgresql-10-U6N320/postgresql-10-10.4/build/../src/backend/commands/analyze.c:545


After a bit of digging it became clear that they hit the libc bug:
https://sourceware.org/bugzilla/show_bug.cgi?id=18441

We decided to switch to libicu collation, but it became clear that 
ANALYZE ignore column level COLLATE and stubbornly use default collation.

It happens thanks to this piece of code:

static void
compute_scalar_stats(VacAttrStatsP stats,
                      AnalyzeAttrFetchFunc fetchfunc,
                      int samplerows,
                      double totalrows)
....
     /* We always use the default collation for statistics */
     ssup.ssup_collation = DEFAULT_COLLATION_OID;
....

Ok, we decided to set libicu collation as default for database, although 
it is a major pain.
But turns out it`s impossible to set libicu collation as default for 
database:

postgres=# create database icu LC_COLLATE = 'en-x-icu';
ERROR:  invalid locale name: "en-x-icu"

Why ANALYZE igrones column COLLATE? It there workaround to force ANALYZE 
to use libicu?

-- 
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company