PostgreSQL查询优化:使用HypoPG插件控制索引

HypoPG是支持虚拟索引的PostgreSQL扩展插件。

虚拟索引是实际上不存在的索引,因此不会消耗CPU、磁盘或任何资源来创建。它们有助于了解特定索引是否可以提高查询的性能,因为您可以知道PostgreSQL是否会使用这些索引,而无需花费资源来创建它们。

安装:

  
  
  
  1. #下载地址:https://github.com/HypoPG/hypopg

  2. #将hypopg-1.4.0.tar.gz文件上传至/opt目录后解压

  3. tar zxvf hypopg-1.4.0.tar.gz

  4. #安装

  5. make

  6. make install

案例:

  
  
  
  1. #建表并插入数据

  2. postgres=# CREATE TABLE wytb01 AS SELECT id, 'wuyang ' || id AS val FROM generate_series(1,10000) id;

  3. SELECT 10000

  4. #统计信息收集

  5. postgres=# ANALYZE wytb01;

  6. ANALYZE

  7. #当前为全表扫描

  8. postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id = 1;

  9. QUERY PLAN

  10. ---------------------------------------------------------

  11. Seq Scan on wytb01 (cost=0.00..152.00 rows=1 width=15)

  12. Filter: (id = 1)

  13. (2 rows)

  14. #创建虚拟索引

  15. postgres=# SELECT * FROM hypopg_create_index('CREATE INDEX ON wytb01 (id)');

  16. indexrelid | indexname

  17. ------------+------------------------

  18. 13624 | <13624>btree_wytb01_id

  19. (1 row)

  20. #查看虚拟索引创建后的执行计划

  21. postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id = 1;

  22. QUERY PLAN

  23. ----------------------------------------------------------------------------------------

  24. Index Scan using "<13624>btree_wytb01_id" on wytb01 (cost=0.04..8.05 rows=1 width=15)

  25. Index Cond: (id = 1)

  26. (2 rows)

  27. #只有在不使用`EXPLAIN ANALYZE`时才会使用虚拟索引

  28. postgres=# EXPLAIN ANALYZE SELECT * FROM wytb01 WHERE id = 1;

  29. QUERY PLAN

  30. -------------------------------------------------------------------------------------------------

  31. Seq Scan on wytb01 (cost=0.00..180.00 rows=1 width=13) (actual time=0.036..6.072 rows=1 loops=1)

  32. Filter: (id = 1)

  33. Rows Removed by Filter: 9999

  34. Planning time: 0.109 ms

  35. Execution time: 6.113 ms

案例二:

  
  
  
  1. #继续上述案例,您可以创建真实索引并运行`EXPLAIN`:

  2. postgres=# SELECT hypopg_reset();

  3. hypopg_reset

  4. --------------

  5. (1 row)

  6. #创建真实索引,查看执行计划

  7. postgres=# CREATE INDEX ON wytb01(id, val);

  8. CREATE INDEX

  9. postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id = 1;

  10. QUERY PLAN

  11. --------------------------------------------------------------------------------------

  12. Index Only Scan using wytb01_id_val_idx on wytb01 (cost=0.29..4.30 rows=1 width=15)

  13. Index Cond: (id = 1)

  14. (2 rows)

  15. #查询计划使用了索引。使用`hypopg_hide_index(oid)`隐藏其中一个索引:

  16. postgres=# SELECT hypopg_hide_index('wytb01_id_val_idx'::REGCLASS);

  17. hypopg_hide_index

  18. -------------------

  19. t

  20. (1 row)

  21. #隐藏后,全表扫描

  22. postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id = 1;

  23. QUERY PLAN

  24. ---------------------------------------------------------

  25. Seq Scan on wytb01 (cost=0.00..152.00 rows=1 width=15)

  26. Filter: (id = 1)

  27. (2 rows)

  28. #也可以隐藏虚拟索引

  29. postgres=# SELECT hypopg_create_index('CREATE INDEX ON wytb01(id)');

  30. hypopg_create_index

  31. --------------------------------

  32. (13625,<13625>btree_wytb01_id)

  33. (1 row)

  34. postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id = 1;

  35. QUERY PLAN

  36. --------------------------------------------------------------------------------------

  37. Index Only Scan using wytb01_id_val_idx on wytb01 (cost=0.29..4.30 rows=1 width=15)

  38. Index Cond: (id = 1)

  39. (2 rows)

  40. postgres=# SELECT hypopg_hide_index(13625);

  41. hypopg_hide_index

  42. -------------------

  43. f

  44. (1 row)

  45. postgres=# EXPLAIN SELECT * FROM wytb01 WHERE id = 1;

  46. QUERY PLAN

  47. -------------------------------------------------------

  48. Seq Scan on wytb01 (cost=0.00..180.00 rows=1 width=13)

  49. Filter: (id = 1)

  50. 您可以使用`hypopg_hidden_indexes()`或视图检查哪些索引被隐藏

1.hypopg不提供扩展升级脚本,因为在创建的任何对象中没有保存数据。因此,您需要首先删除扩展,然后再次创建它以获取新版本。

2.隐藏现有索引的功能仅适用于当前会话中的EXPLAIN命令,并不会影响其他会话。


本文分享自微信公众号 - 开源软件联盟PostgreSQL分会(kaiyuanlianmeng)。
如有侵权,请联系 support@oschina.cn 删除。
本文参与“OSC源创计划”,欢迎正在阅读的你也加入,一起分享。

展开阅读全文
加载中
点击引领话题📣 发布并加入讨论🔥
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部