How to solve common slow SQL problems in PostgreSQL

Developers who have just been familiar with PG will encounter some problems when they touch pg.
The common performance is to bump into some slow SQL. Don't throw up the groove at this time. The vast majority is not a problem, a slight grammatical adjustment or some simple optimization can solve the problem. The following is a detailed analysis of several cases.

One: Index scan of Chinese string is slow
Test =# \d Testidx
Table "Test.testidx"
Column | Type | Modifiers
----------------+-----------------------------+-----------
ID | numeric |
table_id | numeric |
Description | Character varying (4000) |
user_comment | Character varying (4000) |
encoding | Character varying (64) |

This is a very common table, where the database of the Encoding Collate Ctype are zh_cn. UTF-8
In order to retrieve description information, it is very common (www.neitui.me) that we create a btree index on the column.
test=# CREATE index IDX_TESTIDX on TESTIDX (description);
CREATE INDEX

Retrieving the information in a column uses the like statement to match and discovers that the query plan actually does not use the index.


test=# Explain select description from testidxwhere description like ' test% ';
QUERY PLAN
-------------------------------------------------------------
Seqscan on Testidx (cost=0.00..30151.00rows=64 width=28)
Filter: ((description):: Text ~ ~ ' test% ':: Text)
(2 rows)

A little reluctant to stop the index to see exactly, the index is used, but the conditional filter is placed outside the index. The data is retrieved with an index scan, a bitmap is generated, and then the bitmap Heap scan is taken, which is obviously problematic.


test=# set Enable_seqscan=off;
SET
test=# Explain select description from testidxwhere description like ' test% ';
Queryplan
------------------------------------------------------------------------------------
Bitmap Heap Scan on Testidx (cost=29756.57..59907.57 rows=64 width=28)
Filter: ((description):: Text ~ ~ ' test% ':: Text)
-Bitmap Index Scan on Idx_testidx (cost=0.00..29756.55 rows=1000000 width=0)
(3 rows)


The actual execution again, look at the implementation of the situation is not optimistic, 1000000 rows of data are taken out, the heap scanning filter to filter out all the rows
It took nearly half a second to execute the SQL, too slow.


test=# Explain analyze Select description fromtestidx where description like ' test% ';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------- -------------------------
Bitmap Heap Scan on Testidx (cost=29756.57..59907.57 rows=64 width=28) (actual time=407.548..407.548 rows=0 Loops=1)
Filter: ((description):: Text ~ ~ ' test% ':: Text)
Rows removed by filter:1000000
-Bitmap Index Scan onidx_testidx (cost=0.00..29756.55rows=1000000 width=0) (actual time=166.581..166.581 rows=1000 Loops=1)
Total runtime:407.590 ms
(5 rows)

The reason is simple.
1 The columns in the table under UTF8 encoding need to be operated according to UTF8 rules (various operators > =< (~ ~) like etc.)
2 when creating an index, no comparison is specified, and the default string comparison method with standard "C" only supports the = operator on UTF8.
3 When you create an index, you specify a specific comparison method that can support the like of the index.
4 btree Gin Hash index uses the same.
5 PG in char varchar text data type applies the above rules.

The operator classes Text_pattern_ops,varchar_pattern_ops, and bpchar_pattern_ops support B-tree indexes on the Typestext , varchar, and char respectively. The difference from the default operatorclasses is, the values being compared strictly character by character Ratherthan According to the Locale-specific collation rules. This makes Theseoperator classes suitable for use by queries involving pattern matchingexpressions (like or POSIX regular expressions) When the database does isn't usethe standard "C" locale. As an example, you might index a varcharcolumn like this:

Look at the syntax for CREATE INDEX:

CREATE [UNIQUE] INDEX [concurrently] [name] on table_name [USING Method]
({column_name | (expression)} [] [Opclass] [ASC | DESC] [NULLS {First | Last}] [, ...]
[With (Storage_parameter = value [, ...])]
[Tablespace Tablespace_name]
[WHERE predicate]
Collation
The name of the collation to use for theindex. By default, the index uses the collation declared for the column to beindexed or the result collation of the expression to be indexed. Indexes Withnon-default collations can be useful for queries that involve expressions Usingnon-default collations.

You can then use this syntax to create a corresponding index that is available.
test=# CREATE index IDX_TESTIDX2 on Testidx (description varchar_pattern_ops);
CREATE INDEX

Let's take a look at the effect, index cond appears. Using an index scan, and conditional filtering, there could be 64 rows of valid data.
test=# Explain select description from testidxwhere description like ' test% ';

Queryplan
------------------------------------------------------------------------------------
Index only Scan using idx_testidx2 ontestidx (cost=0.55..8.57 rows=64width=28)
Index Cond: ((Description ~>=~ ' Test ':: Text) and (Description ~<~ ' Tesu ':: Text))
Filter: ((description):: Text ~ ~ ' test% ':: Text)
(3 rows)


The actual execution result verifies the validity of the index, and the efficiency of SQL execution is obvious.


test=# Explain analyze Select description fromtestidx where description like ' test% ';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------- --------------
Index only Scan using idx_testidx2 ontestidx (cost=0.55..8.57 rows=64width=28) (actual time=0.081..0.081 rows=0 Loops=1)
Index Cond: ((Description ~>=~ ' Test ':: Text) and (Description ~<~ ' Tesu ':: Text))
Filter: ((description):: Text ~ ~ ' test% ':: Text)
Heap fetches:0
Total runtime:0.105 ms
(5 rows)

How to solve common slow SQL problems in PostgreSQL