数据行较大时不使用PostgreSQL索引 [英] PostgreSQL Index not used when data rows are large

问题描述

我很好奇为什么数据行即使大到100时索引也不起作用.

Hi I'm curious about why index doesn't work when data rows are large even 100.

这里选择10个数据:

mydb> explain select * from data where user_id=1;
+-----------------------------------------------------------------------------------+
| QUERY PLAN                                                                        |
|-----------------------------------------------------------------------------------|
| Index Scan using ix_data_user_id on data  (cost=0.14..8.15 rows=1 width=2043) |
| Index Cond: (user_id = 1)                                                         |
+-----------------------------------------------------------------------------------+
EXPLAIN

这里选择了100个数据:

Here's select for 100 data:

mydb> explain select * from data where user_id=1;
+------------------------------------------------------------+
| QUERY PLAN                                                 |
|------------------------------------------------------------|
| Seq Scan on data  (cost=0.00..44.67 rows=1414 width=945) |
| Filter: (user_id = 1)                                      |
+------------------------------------------------------------+
EXPLAIN

当数据行为100时，索引如何工作?

How can index work when data rows are 100?

推荐答案

100不是大量数据.将10,000或100,000行视为可观的金额.

100 is not a large amount of data. Think 10,000 or 100,000 rows for a respectable amount.

简单地说，表中的记录存储在数据页上.数据页通常约有8k字节(取决于数据库和设置).索引的主要目的是减少需要读取的数据页的数量.

To put it simply, records in a table are stored on data pages. A data page typically has about 8k bytes (it depends on the database and on settings). A major purpose of indexes is to reduce the number of data pages that need to be read.

如果表中的所有记录都适合一页，则无需减少正在读取的页数.一页将被读取.因此，索引可能不是特别有用.

If all the records in a table fit on one page, there is no need to reduce the number pages being read. The one page will be read. Hence, the index may not be particularly useful.

这篇关于数据行较大时不使用PostgreSQL索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！