PostgreSQL索引探究

概述创建包含10个列（c01 - c10）的表my_table，用如下语句创建2个索引，并向表中插入6w条记录。这6w条记录的c01列，全部都是2017年04月21日的数据。 CREATE INDEX my_table_index1 ON my_table USING btree (c05, c01, c02) TABLESPACE smart_history_index;CREATE INDEX

创建包含10个列（c01 - c10）的表my_table，用如下语句创建2个索引，并向表中插入6w条记录。这6w条记录的c01列，全部都是2017年04月21日的数据。

CREATE INDEX my_table_index1 ON my_table USING btree (c05,c01,c02) tableSPACE smart_history_index;CREATE INDEX my_table_index2 ON my_table USING btree (c01,c02,c03,c04,c05) tableSPACE smart_history_index;

使用以下sql1语句查询，查询时间为2分钟。
sql1：

select * from my_table t1 where (select count(*) from my_table t2 where ((t1.c01 = t2.c01 ) or (t1.c01 is null and t2.c01 is null)) and ((t1.c02 = t2.c02 ) or (t1.c02 is null and t2.c02 is null)) and ((t1.c03 = t2.c03 ) or (t1.c03 is null and t2.c03 is null)) and ((t1.c04 = t2.c04 ) or (t1.c04 is null and t2.c04 is null)) and ((t1.c05 = t2.c05 ) or (t1.c05 is null and t2.c05 is null)) and ((t1.c06 = t2.c06 ) or (t1.c06 is null and t2.c06 is null)) and ((t1.c07 = t2.c07 ) or (t1.c07 is null and t2.c07 is null)) and ((t1.c08 = t2.c08 ) or (t1.c08 is null and t2.c08 is null)) and ((t1.c09 = t2.c09 ) or (t1.c09 is null and t2.c09 is null)) and ((t1.c10 = t2.c10 ) or (t1.c10 is null and t2.c10 is null)) )>1;

使用以下sql1语句查询，查询时间为2.8秒。
sql2：

select * from my_table t1 where (select count(*) from my_table t2 where ((t1.c01 = t2.c01 ) or (t1.c01 is null and t2.c01 is null)) and ((t1.c02 = t2.c02 ) or (t1.c02 is null and t2.c02 is null)) and ((t1.c03 = t2.c03 ) or (t1.c03 is null and t2.c03 is null)) and ((t1.c04 = t2.c04 ) or (t1.c04 is null and t2.c04 is null)) and ((t1.c05 = t2.c05 ) or (t1.c05 is null and t2.c05 is null)) and ((t1.c06 = t2.c06 ) or (t1.c06 is null and t2.c06 is null)) and ((t1.c07 = t2.c07 ) or (t1.c07 is null and t2.c07 is null)) and ((t1.c08 = t2.c08 ) or (t1.c08 is null and t2.c08 is null)) and ((t1.c09 = t2.c09 ) or (t1.c09 is null and t2.c09 is null)) and ((t1.c10 = t2.c10 ) or (t1.c10 is null and t2.c10 is null)) and (c01 >= '2017-04-20 00:00:00' and c01 < '2017-04-21 00:00:00') )>1 and (c01 >= '2017-04-20 00:00:00' and c01 < '2017-04-21 00:00:00');

虽然sql2最后对于c01列的where条件并为实质上减少过滤出的数据量。但是能够显著的提高查询效率（60倍），进一步使用以下sql3语句监视索引使用情况发现，sql2只调用了my_table_index2，而sql1既调用了my_table_index1也调用了my_table_index2。
sql3：

select relname,indexrelname,IDx_scan,IDx_tup_read,IDx_tup_fetch from pg_stat_user_indexes where relname = 'my_table' order by IDx_scan asc,IDx_tup_read asc,IDx_tup_fetch asc;

显然my_table_index1肯定比my_table_index2的辨识度更高。之所以sql优化解析器会做出这样的选择，猜测可能是因为my_table_index2中列c01处于第一个的位置，而sql2最后的c01查询条件刚好暗示强化了解释器去选择my_table_index2。
在此基础上我进行了进一步的实验：如果drop掉my_table_index1只保留my_table_index2，则无论是sql1还是sql2都能达到大约2.8秒的查询速度；如果drop掉my_table_index2只保留my_table_index1，则无论是sql1还是sql2都需要2分钟才能完成查询；当然my_table_index1也并不是一无是处，如果将两个索引全部drop掉，那么做一次查询（全表扫描）大约需要10分钟。

最后说两点启示： 1.索引不能随便建，如果建的不好，不仅影响插入效率，也会影响查询效率。 2.SQL语句优化之路，任重而道远。

总结

以上是内存溢出为你收集整理的PostgreSQL索引探究全部内容，希望文章能够帮你解决PostgreSQL索引探究所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。