GiST index for pgtrgm bloats a lot

Поиск
Список
Период
Сортировка
От hubert depesz lubaczewski
Тема GiST index for pgtrgm bloats a lot
Дата
Msg-id 20150518121250.GA19976@depesz.com
обсуждение исходный текст
Ответы Re: GiST index for pgtrgm bloats a lot  (Heikki Linnakangas <hlinnaka@iki.fi>)
Список pgsql-bugs
Hi,
We have this situation on many servers - used versions are 9.1.14 and/or
9.3.6, on Linux servers.

There is a table, with ~ 1 million rows.
There are no deletes there, inserts happen at the rate of ~ 100-200
daily (but not spread, it's usually a single moment in day where there
happen the inserts).
There is also HUGE number of updates - up to 200,000 rows updated per
hour.

Schema of the table:
                                                      Table "schemaXX.tableYY"
                Column                |            Type             |                           Modifiers
            

--------------------------------------+-----------------------------+---------------------------------------------------------------
 id                                   | bigint                      | not null default
nextval('schemaXX.tableYY_id_seq'::regclass)
 col_a                                | character varying(255)      |
 ...............................      | bigint                      | not null
 ...............................      | character varying(255)      |
 ...............................      | bigint                      |
 ...............................      | character varying(255)      | not null
 ...............................      | character varying(255)      |
 ...............................      | timestamp without time zone |
 ...............................      | timestamp without time zone |
 ...............................      | bigint                      |
 ...............................      | boolean                     |
 ...............................      | boolean                     |
 ...............................      | timestamp without time zone |
 ...............................      | timestamp without time zone |
 ...............................      | boolean                     |
 ...............................      | text                        |
 ...............................      | boolean                     | default false
 ...............................      | character varying(255)      |
 ...............................      | bigint                      |
 ...............................      | boolean                     | default true
 col_c                                | character varying(255)      |
 ...............................      | character varying(255)      | default '----'::character varying
 ...............................      | bigint                      |
 ...............................      | bigint                      | not null
 ...............................      | bigint                      | not null
 col_b                                | character varying(255)      |
 ...............................      | bigint                      |
 ...............................      | boolean                     |
 ...............................      | boolean                     |
 ...............................      | bigint                      |
 ...............................      | text                        |
 ...............................      | boolean                     |
 ...............................      | text                        |
 ...............................      | boolean                     |
 ...............................      | character varying(255)      |
 ...............................      | boolean                     |
 ...............................      | boolean                     |
 ...............................      | bigint                      |
 ...............................      | character varying(255)      |
 ...............................      | text                        |
 ...............................      | bigint                      |
 ...............................      | text                        |
 ...............................      | text                        |
 ...............................      | character varying(255)      |
 ...............................      | integer                     |
 ...............................      | character varying(255)      |
 ...............................      | character varying(255)      |
 ...............................      | character varying(255)      |
 ...............................      | bigint                      |

indexes:
There are 17 indexes in total, and the problematic is:

"index_trgm_courses_composite_search" gist (((((COALESCE(lower(col_a::text), ''::text) || ' '::text) ||
COALESCE(lower(col_b::text),''::text)) || ' '::text) || COALESCE(lower(col_c::text), ''::text)) gist_trgm_ops) 

On 2015-04-27 this index was reindexed. It's size (relpages as shown in
pg_class) dropped from 152698 to 45865.  Since then the index is consistently
increasing in size. Today it was 343370 pages, and after reindex it dropped to
29091.

On these databases we have some (not many) 1-2 minute transactions, but usually
every transaction within 30 seconds

This table is vacuumed (autovacuum) quite a lot, usually at least 10 times a
day.

Is there anything we could do to help diagnose the problem, and fix it?
I don't think I can install custom pg version, and downtime would be
complicated to get approval, but anything about the data/database I can
check that would allow diagnosing the bug, I'd be happy to do.

depesz

--
The best thing about modern society is how easy it is to avoid contact with it.
                                                             http://depesz.com/

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Peter Geoghegan
Дата:
Сообщение: Re: BUG #13306: PostgreSQL 9.5 change to array type changes broke PostGIS build
Следующее
От: Heikki Linnakangas
Дата:
Сообщение: Re: GiST index for pgtrgm bloats a lot