PostgreSQL 9.6 Build multi-column index test

Building a test table structure

CREATE TABLE T_test

(

ID Integer,

Name text COLLATE pg_catalog. " Default ",

Address character varying ($) COLLATE pg_catalog. " Default

);

Inserting test data

Insert INTO T_test SELECT generate_series (1,10000000) as key, ' name ' | | (Random () * (10^3)):: Integer, ' Changan Street NO ' | | (Random () * (10^3)):: integer;

Creating 3-Column Indexes

Create INDEX idx_t_test_id_name_address on t_test (id,name,address);

1. The following query statements can use an index and a faster

The first column of the index is in the where statement, regardless of the condition order

Typically 3 milliseconds more results

Explain analyze select * from T_test where ID < + and name like ' name% ' and address like ' changan% ';

Explain analyze select * from t_test where address like ' changan% ' and name ' name% ' and ID < 2000;

Explain analyze select * from t_test where name like ' name% ' and ID < + and address like ' changan% ';

Explain analyze select * from T_test where ID < 2000

Explain analyze select * from t_test where name like ' name% ' and ID < 2000

Explain analyze select * from t_test where address like ' changan% ' and ID < 2000;

Explain analyze select * from t_test where address like ' changan% ' and name ' name% ' and ID < 2000;

2. The following indexes can be used, but the query is slow

Index first column in order by

Explain analyze select * from T_test where the address like ' changan% ' and name is ' name% ' ORDER by ID;

17S

Explain analyze select * from t_test where address like ' changan% ' ORDER by ID;

8s

Explain analyze select * from t_test where name "Name% ' ORDER by ID;

9s

The following statement cannot use the index, the first column of the index is not in the Where or order by

Explain analyze select * from T_test where the address like ' changan% ' and name is like ' name% ';

Explain analyze select * from t_test where address like ' changan% ';

Explain analyze select * from T_test where name is like ' name% ';

Create a double-column index

Create INDEX idx_t_test_name_address on t_test (name,address);

The following statement uses the index

Explain analyze select * from t_test where name = ' name580 ';

Explain analyze select * from T_test where the address like ' changan% ' and name is like ' name580 ';

Explain analyze select * from t_test where address like ' changan% ' and name = ' name580 ';

The following statement does not use the index

Explain analyze select * from t_test where name like ' name% '

Explain analyze select * from t_test where address like ' changan% '

Explain analyze select * from t_test where address = ' Changan Street NO416 '

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1873178

PostgreSQL 9.6 Build multi-column index test