pgsql挺好玩的,可以创建部分索引
部分索引 是建立在一个表的子集上的索引;该子集是由一个条件表达式定义的(叫做部分索引的谓词 )。该索引只包含表中那些满足这个谓词的行。部分索引是一个特殊的特性,但是在某些场合很有用。
部分索引的主要动机是为了避免对普通数值(大量重复的数值)建立索引。因为在普通数值上的查询就算使用索引也没什么好处,那么还不如从索引中剔除这些大量重复的行。这样可以减小索引尺寸,提高那些真正使用索引的查询的速度。同时它也能提高更新操作的速度,因为不是所有情况都需要更新索引。
今天在折腾中发现如果对col使用部分索引在ORDER BY col时可能导致全表扫描该字段。pgsql v8.1, 30w条记录。
1、正常创建索引,order by时使用该索引
CREATE INDEX ftype on table (fee_type);
explain SELECT * FROM test_fee ORDER BY fee_type LIMIT 10
Limit (cost=0.00..0.53 rows=10 width=259)
-> Index Scan using ftype on test_fee (cost=0.00..15882.07 rows=301596 width=259)
Time: 0.015s
explain analyze SELECT * FROM test_fee ORDER BY fee_type LIMIT 10
Limit (cost=0.00..0.53 rows=10 width=259) (actual time=0.049..0.139 rows=10 loops=1)
-> Index Scan using ftype on test_fee (cost=0.00..15881.26 rows=301596 width=259)
(actual time=0.042..0.077 rows=10 loops=1)
Total runtime: 0.263 ms
2、创建部分索引,结果全表扫描
CREATE INDEX ftype ON table (fee_type) WHERE fee_type > 0;
SELECT * FROM test_fee ORDER BY fee_type LIMIT 10
Limit (cost=18217.34..18217.37 rows=10 width=259)
-> Sort (cost=18217.34..18971.33 rows=301596 width=259)
Sort Key: fee_type
-> Seq Scan on test_fee (cost=0.00..11699.96 rows=301596 width=259)
Time: 0.015s
explain analyze SELECT * FROM test_fee ORDER BY fee_type LIMIT 10
Limit (cost=18217.34..18217.37 rows=10 width=259) (actual time=1994.620..1994.701 rows=10 loops=1)
-> Sort (cost=18217.34..18971.33 rows=301596 width=259) (actual time=1994.611..1994.637 rows=10 loops=1)
Sort Key: fee_type
Sort Method: top-N heapsort Memory: 19kB
-> Seq Scan on test_fee (cost=0.00..11699.96 rows=301596 width=259) (actual time=0.060..1022.666 rows=301596 loops=1)
Total runtime: 1994.863 ms
即使fee_type值都大于0(符合部分索引条件),结果一样。
总结:对使用了部分索引的栏目进行排序时pgsql可能会全表扫描导致性能骤降。除非你在WHERE条件中排除未被索引值的row,比如创建了一个 (fid > 9) 的部分索引,那么条件为 “ WHERE fid > 9 ORDER BY fid” 或 “ WHERE fid > 10 ORDER BY fid”就会使用fid上建立的(部分)索引。
分享到:
相关推荐
在PostgreSQL数据库管理...如果你需要查询其他数据库对象,例如索引或视图,PostgreSQL也提供了相应的系统目录视图和函数,如`pg_indexes`和`pg_views`。熟悉这些系统目录视图和函数对于管理PostgreSQL数据库至关重要。
- **主键**:DB2中的主键定义与GreenPlum/PostgreSQL相似,但需要注意的是,在GreenPlum/PostgreSQL中,主键约束通常会自动创建一个唯一索引。 - **外键**:DB2支持外键约束,但在GreenPlum/PostgreSQL中,外键约束...
一个PostgreSQL实例可以包含多个数据库,每个数据库又由多个模式组成,模式作为一个命名空间,方便组织和管理数据库对象,如表和索引。 第四至六章主要关注数据存储和维护。如何创建、修改和删除数据表是日常数据库...
- 查询数据:掌握`SELECT`语句的各种用法,包括聚合函数(COUNT、SUM、AVG等)、排序(ORDER BY)和分组(GROUP BY)。 5. **更新与删除数据**: - 更新记录:使用`UPDATE`语句修改表中的数据。 - 删除记录:...
- 逻辑复制是PostgreSQL 9.6的一个重大更新,它提供了订阅/发布模型,允许用户仅复制数据库中的特定表或部分数据,而非整个数据库,这对于分布式系统和数据同步非常有用。 4. **多版本并发控制(MVCC)的改进**: ...
了解执行计划还有助于识别可能的性能瓶颈,比如全表扫描、数据的物理布局以及如何高效使用索引。 在文档的开头,作者提到了“Slidlicense”,这可能是指有关文档的版权许可信息。例如Creative Commons BY-NC-SA...
**基于PostgreSQL的基本SQL语句** ...以上只是PostgreSQL基本SQL语句的一部分,实际使用中还有更复杂的查询和高级特性,如窗口函数、存储过程、触发器等。通过不断学习和实践,你将能更好地掌握PostgreSQL的使用。
SELECT 语句用于查询,可以通过 JOIN、GROUP BY、HAVING、ORDER BY 和子查询等来实现复杂的数据检索。 四、索引与性能优化 索引是提升查询性能的关键。PostgreSQL 支持 B-Tree、Hash、GiST、SP-GiST、GIN 和 BRIN ...
4. **查询语言**:深入理解SELECT语句,包括联接(JOIN)、子查询、聚合函数(COUNT、SUM等)、分组(GROUP BY)和排序(ORDER BY)等高级查询技巧。 5. **索引与性能**:学习如何创建和管理索引以优化查询性能,...
- **控制行的顺序**:使用`ORDER BY`排序。 - **消除重复数据**:使用`DISTINCT`去除重复项。 - **执行计算**:利用聚合函数进行计算。 - **选择行**:根据特定条件筛选数据。 - **使用更复杂的条件**:如逻辑运算符...
- **排序**: `SELECT * FROM 表名 ORDER BY 字段 [ASC | DESC];` - 示例: `SELECT * FROM student ORDER BY id DESC;` - **分组查询**: `SELECT 字段, COUNT(*) FROM 表名 GROUP BY 字段;` - 示例: `SELECT age,...
2. **排序与分页**:ORDER BY和LIMIT子句的应用。 3. **子查询**:嵌套查询的编写,以及IN、EXISTS等子查询的使用。 4. **视图**:创建、修改和删除视图,理解视图在数据库设计中的作用。 5. **索引**:理解索引的...
可以通过WHERE子句进行条件筛选,GROUP BY和HAVING用于分组和过滤,ORDER BY用于排序,JOIN操作用于连接多个表。 4. **数据库事务**:了解如何使用BEGIN、COMMIT和ROLLBACK语句来管理事务,确保数据的一致性和完整性...
5. **排序**:使用 `ORDER BY` 语句按指定字段对结果进行排序。 6. **窗口函数**:提供基于行集的计算,如 `RANK`, `ROW_NUMBER`, `LEAD`, `LAG` 等。 **四、PostgreSQL 8 的特性** 1. **触发器**:允许在特定事件...
通过调整参数、合理设计表结构、使用索引、优化SQL语句等手段,提升PostgreSQL的性能。监控工具如pg_stat_activity可查看数据库活动。 九、安全性与权限管理 PostgreSQL提供了用户、角色和权限管理机制,确保数据...
本节介绍了窗口函数的使用方法,包括 OVER 子句、PARTITION BY 和 ORDER BY 等关键字的使用。 ##### 3.6 更多高级特性 除了上述内容外,文档还介绍了更多高级特性,如存储过程、触发器、索引等,这些特性将进一步...
- 查询语句:SELECT,用于从表中检索数据,支持JOIN、WHERE、GROUP BY、HAVING、ORDER BY等子句。 - DML操作:INSERT用于插入数据,UPDATE用于更新数据,DELETE用于删除数据。 - DDL操作:CREATE TABLE用于创建表...
然而,这个例子中,虽然索引被用来加速查询,但在其他情况下,如果查询条件与索引不完全匹配,或者查询条件过于复杂,那么索引可能无法发挥其应有的作用,甚至可能导致全表扫描,反而降低了性能。例如,如果查询条件...
4. **查询数据**:掌握 `SELECT` 语句,学习如何使用 `WHERE` 过滤数据,`GROUP BY` 进行分组,`ORDER BY` 排序,以及 `JOIN` 连接多个表。 **四、高级SQL** 1. **子查询**:理解如何在查询中嵌套查询,利用子查询...
解压 `postgresql-15.0` 文件后,我们可以看到 PostgreSQL 的源代码结构,主要分为以下几个部分: - **src/backend**:包含了数据库内核的主要实现,如查询解析器、存储管理、事务处理等模块。 - **src/include**:...