From: Grigory Smolkin <g.smolkin@postgrespro.ru>
To: pgsql-bugs@lists.postgresql.org, Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
Subject: Re: long analyze, libc bug and libicu
Date: Sat, 7 Jul 2018 12:18:59 +0300
Message-ID: <2da0c289-bfc8-a40f-e13b-1b2943746e9e@postgrespro.ru> (raw)
In-Reply-To: <ca8fc598-da02-d987-4e5c-8c249df9de40@2ndquadrant.com>
References: <dc49b0d2-d20d-adeb-ecdb-0ad25461386d@postgrespro.ru>
<ca8fc598-da02-d987-4e5c-8c249df9de40@2ndquadrant.com>
On 07/07/2018 10:10 AM, Peter Eisentraut wrote:
> On 05.07.18 17:05, Grigory Smolkin wrote:
>> Why ANALYZE igrones column COLLATE?
> I think the statistics would be mostly the same independent of which
> collation you use. This could possibly be refined, but I don't think
> it's a major problem right now.
>
Thank you for your interest in this problem!
> I think the statistics would be mostly the same independent of which
> collation you use.
I assumed that one of the goals of using libicu is to be independent
from libc collation and it`s bugs and inconsistencies, but current
ANALYZE forced to use libc anyway, which undermines that goal.
> This could possibly be refined, but I don't think
> it's a major problem right now.
It`s a major problem to people, who use Thai alphabet.
In attachment there is a data sample(33MB on my machine). ANALYZE`ing it
comes up with following results:
postgres=# ANALYZE t_icu_coll;
ANALYZE
Time: 2252086.648 ms
37minutes on 33MB table is painful. On big tables autovacuum ANALYZE
goes for hours, starving autovacuum VACUUM for worker
slots(autovacuum_max_workers).
Another major problem is that in strol_l() backend process ignores
pg_terminate_backend()/pg_cancel_backend() functions.
With attached patch this problem goes away:
postgres=# analyze t_icu_coll;
ANALYZE
Time: 161.419 ms
--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
Attachments:
t_icu_coll.sql.gz (application/gzip, 3.2M) downloadanalyze_honour_column_collation.patch (text/x-patch, 674B) downloaddiff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.cindex 848263d..88dda65 100644--- a/src/backend/commands/analyze.c+++ b/src/backend/commands/analyze.c@@ -2291,8 +2291,12 @@ compute_scalar_stats(VacAttrStatsP stats,
memset(&ssup, 0, sizeof(ssup));
ssup.ssup_cxt = CurrentMemoryContext;
- /* We always use the default collation for statistics */- ssup.ssup_collation = DEFAULT_COLLATION_OID;+ /* Try to use column collation for statistics */+ if (stats->attr->attcollation == InvalidOid)+ ssup.ssup_collation = DEFAULT_COLLATION_OID;+ else+ ssup.ssup_collation = stats->attr->attcollation;+
ssup.ssup_nulls_first = false;
/*
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: g.smolkin@postgrespro.ru, peter.eisentraut@2ndquadrant.com
Subject: Re: long analyze, libc bug and libicu
In-Reply-To: <2da0c289-bfc8-a40f-e13b-1b2943746e9e@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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