PostgreSQL page inspect diagnoses and optimizes the query performance degradation caused by the delay of GIN (inverted) index merging

Label

PostgreSQL, brin index, gin index, merge delay, gin_pending_list_limit, query performance degradation

background

GIN index is an inverted index of PostgreSQL database with multi-value type. A record may involve KEY in multiple GIN indexes. So if index is merged in real time when writing, IO will increase sharply and RT will increase inevitably. In order to improve write throughput, PG allows users to open the delay merging technology of GIN index. After opening, data will be written to pending list first, not directly to index page. When pending list reaches a certain size, or autovacuum corresponding table, the action of merging pending list into index will be triggered.

When querying, if PENDING LIST is not merged into the index, pending list is queried, and information about the index is also queried.

If the amount of writing is large, pending list is very huge, and the speed of merging (done by autovacuum worker) can not keep up with it, the query performance will be degraded when querying through GIN index.

Knowing the root cause of the problem, you know how to solve it and how to investigate it.

Background principle

https://www.postgresql.org/docs/11/static/sql-createindex.html

GIN indexes accept different parameters:

1,fastupdate

This setting controls usage of the fast update technique described in Section 66.4.1. It is a Boolean parameter: ON enables fast update, OFF disables it. (Alternative spellings of ON and OFF are allowed as described in Section 19.1.) The default is ON.

Note

Turning fastupdate off via ALTER INDEX prevents future insertions from going into the list of pending index entries, but does not in itself flush previous entries. You might want to VACUUM the table or call gin_clean_pending_list function afterward to ensure the pending list is emptied.

2,gin_pending_list_limit

Custom gin_pending_list_limit parameter. This value is specified in kilobytes.

Current settings

postgres=# show gin_pending_list_limit ;  
 gin_pending_list_limit   
------------------------  
 4MB  
(1 row)

BRIN indexes accept different parameters:

1,pages_per_range

Defines the number of table blocks that make up one block range for each entry of a BRIN index (see Section 67.1 for more details). The default is 128.

2,autosummarize

Defines whether a summarization run is invoked for the previous page range whenever an insertion is detected on the next one.

The pending list and other contents of the index can be observed by page inspect.

https://www.postgresql.org/docs/11/static/pageinspect.html

postgres=# create extension pageinspect ;  
CREATE EXTENSION

Example

1. Build tables

postgres=# create table t(id int, arr int[]);  
CREATE TABLE

2. Create inverted index

postgres=# create index idx_t_1 on t using gin (arr);  
CREATE INDEX

3. Create functions that generate random arrays

postgres=# create or replace function gen_rand_arr() returns int[] as $$  
  select array(select (100*random())::int from generate_series(1,64));  
$$ language sql strict;  
CREATE FUNCTION

4. Write test data

postgres=# insert into t select generate_series(1,100000), gen_rand_arr();  
INSERT 0 100000  
postgres=# insert into t select generate_series(1,1000000), gen_rand_arr();  
INSERT 0 1000000

5. Looking at the pending list size of the current GIN index through the page inspect plug-in, we can see that there are 356 pending page s, involving 2484 records.

If many entries are recorded in pending list, the query performance will decrease significantly.

postgres=# SELECT * FROM gin_metapage_info(get_raw_page('idx_t_1', 0));  
 pending_head | pending_tail | tail_free_size | n_pending_pages | n_pending_tuples | n_total_pages | n_entry_pages | n_data_pages | n_entries | version   
--------------+--------------+----------------+-----------------+------------------+---------------+---------------+--------------+-----------+---------  
            2 |          369 |           3640 |             356 |             2848 |             2 |             1 |            0 |         0 |       2  
(1 row)

6. Query Test 1, (pending list greater than 0)

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t where arr @> array[1,2,3];  
                                                         QUERY PLAN                                                           
----------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.t  (cost=82.38..262.28 rows=11373 width=284) (actual time=82.444..141.559 rows=114906 loops=1)  
   Output: id, arr  
   Recheck Cond: (t.arr @> '{1,2,3}'::integer[])  
   Heap Blocks: exact=41304  
   Buffers: shared hit=42043  
   ->  Bitmap Index Scan on idx_t_1  (cost=0.00..79.92 rows=11373 width=0) (actual time=75.902..75.902 rows=114906 loops=1)  
         Index Cond: (t.arr @> '{1,2,3}'::integer[])  
         Buffers: shared hit=739    
 Planning Time: 0.092 ms  
 Execution Time: 152.260 ms  
(10 rows)

7. vacuum table, mandatory merge pending list

set vacuum_cost_delay=0;  
  
postgres=# vacuum t;  
VACUUM

8. After observing pendign list merging, n_pending_tuples equals 0.

postgres=# SELECT * FROM gin_metapage_info(get_raw_page('idx_t_1', 0));  
 pending_head | pending_tail | tail_free_size | n_pending_pages | n_pending_tuples | n_total_pages | n_entry_pages | n_data_pages | n_entries | version   
--------------+--------------+----------------+-----------------+------------------+---------------+---------------+--------------+-----------+---------  
   4294967295 |   4294967295 |              0 |               0 |                0 |          9978 |            41 |         9421 |       101 |       2  
(1 row)

9. Query Test 2, (pending list = 0)

postgres=# explain (analyze,verbose,timing,costs,buffers) select * from t where arr @> array[1,2,3];  
                                                          QUERY PLAN                                                            
------------------------------------------------------------------------------------------------------------------------------  
 Bitmap Heap Scan on public.t  (cost=792.36..1699.10 rows=117244 width=284) (actual time=79.861..139.603 rows=114906 loops=1)  
   Output: id, arr  
   Recheck Cond: (t.arr @> '{1,2,3}'::integer[])  
   Heap Blocks: exact=41304  
   Buffers: shared hit=41687  
   ->  Bitmap Index Scan on idx_t_1  (cost=0.00..766.95 rows=117244 width=0) (actual time=73.360..73.360 rows=114906 loops=1)  
         Index Cond: (t.arr @> '{1,2,3}'::integer[])  
         Buffers: shared hit=383   -- Substantially reduced   
 Planning Time: 0.135 ms  
 Execution Time: 150.656 ms  
(10 rows)

Similarly, brin has a similar situation.

The method is similar.

Summary

In order to reduce the increase of write RT introduced by index, the database adopts the method of delayed merging. If the long-term write pressure of the database is huge, it may lead to a large number of unincorporated LIST, which will affect the query performance.

Using the pageinspect plug-in, you can see how large pending list s are not merged.

vacuum can be used to enforce the merging of pending list s to improve query performance.

Reference resources