Assistant Engineer
Assistant Engineer
  • UID634
  • Fans0
  • Follows0
  • Posts44

[PostgreSQL Development]PostgreSQL Troubleshooting: Slow SQL

More Posted time:Jul 20, 2016 13:53 PM
PostgreSQL Troubleshooting: Slow SQL
New PG users may have some problems when using the PG.
Slow SQL is a common problem during the usage of PostgreSQL. The problem can be solved through syntactic adjustment or optimization. Some case are analyzed in the following.

I.  The scan of the indexes of Chinese characters is slow.
test =# \d testidx
Column                      Type                               Modifiers
id                           numeric                                
table_id                 numeric                                
description           character varying(4000)      
user_comment    character varying(4000)      
encoding               character varying(64)          

It is a common table. The Encoding Collate Ctype of the database where the table is located is zh_CN.UTF-8.  
To search for the description information, I created a B-tree index on the column.
test=# create index  idx_testidx on testidx(description);                    

I used the like statement for mapping when I searched for desired information in the column. It was found that the query plan used no 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)

When this problem occurred, I disabled the index and found that though the index was used, the conditional filter was placed outside the index. Data was obtained through index scan, and a bitmap was generated. After that, Bitmap Heap Scan was executed. It was obvious that the process was incorrect.

test=# set enable_seqscan=off;
test=# explain select description from testidxwhere description like 'test%';
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)

I performed the scan, but the scan results were not satisfying. 1,000,000 rows of data were extracted. The filter of heap scan filtered out all rows.
It takes almost half a second to execute the SQL. The speed is too low.

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=1000000 loops=1)
Total runtime: 407.590 ms
(5 rows)

The reasons are as follows:
1 With the UTF-8 code, the table columns must be operated based on the
UTF-8 rules. (The operators include  > =< (~~), and like. )
2 A comparison method is not specified during index creation. By default, the standard "C" string comparison method is used, but this method only supports the "=" operator in UTF-8. =
3 The comparison method specified during index creation must support the "like" operator of the index.
4 The usage of the B-tree GIN hash index is similar.
5 The preceding rules are applicable to the char varchar text data type in PostgreSQL.

The operator classes text_pattern_ops,varchar_pattern_ops, and bpchar_pattern_ops support Btree indexes on the typestext, varchar, and char respectively. The difference from the default operatorclasses is that the values are compared strictly character by character ratherthan according to the localespecific collation rules. This makes theseoperator classes suitable for use by queries involving pattern matchingexpressions (LIKE or POSIX regular expressions) when the database does not usethe standard "C" locale. As an example, you might index a varcharcolumn like this:

The syntax of create index is as follows:

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 ]
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 use the syntax to create an available index.
test=# create index  idx_testidx2 on testidx(description varchar_pattern_ops);

The results show the existence of Index Cond. Index scan and conditional filter are executed. There may be 64 rows of valid data.
test=# explain select description from testidxwhere description like 'test%';  

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 execution results show that the index is valid. The SQL ran efficiently.

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)
[Cloudy edited the post at Aug 1, 2016 14:32 PM]