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