Postgresql 9.6 Multi-column Index Test

Establish the structure of the test table

CREATE TABLE t_test

(

Id integer

Name text COLLATE pg_catalog. "default"

Address character varying COLLATE pg_catalog. "default"

);

Insert test data

Insert into t_test SELECT generate_series (1m 10000000) as key, 'name' | | (random () * (10 ^ 3)):: integer,' ChangAn Street NO' | | (random () * (10 ^ 3)):: integer

Build a 3-column index

Create index idx_t_test_id_name_address on t_test (id,name,address)

1. The following query statement can be indexed and faster

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

Generally, the result is more than 3 milliseconds.

Explain analyze select * from t_test where id < 2000 and name like 'name%' and address like' ChangAn%'

Explain analyze select * from t_test where address like 'ChangAn%' and name like' name%' and id < 2000

Explain analyze select * from t_test where name like 'name%' and id < 2000 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 like' name%' and id < 2000

two。 The following indexes can be used, but the query speed is slow

The first column of the index is in order by

Explain analyze select * from t_test where address like 'ChangAn%' and name like' 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 like 'name%' order by id

9s

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

Explain analyze select * from t_test where address like 'ChangAn%' and name like' name%'

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

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

Build a two-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 address like 'ChangAn%' and name like' name580'

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

The following statement does not use an 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'