PostgreSQL为什么不在小表上使用索引？ [英] Why is PostgreSQL not using my indexes on a small table?

问题描述

我在PostgreSQL中有下表：

I have the following table in PostgreSQL:

CREATE TABLE index_test
(
    id int PRIMARY KEY NOT NULL,
    text varchar(2048) NOT NULL,
    last_modified timestamp NOT NULL,
    value int,
    item_type varchar(2046)
);
CREATE INDEX idx_index_type ON index_test ( item_type );
CREATE INDEX idx_index_value ON index_test ( value )

我进行以下选择：

explain select * from index_test r where r.item_type='B';
explain select r.value from index_test r where r.value=56;

执行计划的说明如下：

Seq Scan on index_test r  (cost=0.00..1.04 rows=1 width=1576)
    Filter: ((item_type)::text = 'B'::text)'

据我了解，这是一个全表扫描。问题是：为什么不使用我的索引？

As far as I understand, this is a full table scan. The question is: why my indexes are not used?

可能是因为我的表中的行太少了吗？我只有二十个。您能否为我提供一条SQL语句，以便使用随机数据轻松填充我的表以检查索引问题？

May be, the reason is that I have too few rows in my table? I have only 20 of them. Could you please provide me with a SQL statement to easily populate my table with random data to check the indexes issue?

我找到了这篇文章： http://it.toolbox.com/blogs / db2luw /如何轻松填充带有随机数据的表7888 ，但这对我不起作用。语句的效率并不重要，只有简单。

I have found this article: http://it.toolbox.com/blogs/db2luw/how-to-easily-populate-a-table-with-random-data-7888, but it doesn't work for me. The efficiency of the statement does not matter, only the simplicity.

推荐答案

也许原因是表中的行太少了？

是的。对于一个表中的总共20行，seq扫描总是比索引扫描快。可能这些行无论如何都位于单个数据库块中，因此seq扫描仅需要单个I / O操作。

Yes. For a total of 20 rows in a table a seq scan is always going to be faster than an index scan. Chances are that those rows are located in a single database block anyway, so the seq scan would only need a single I/O operation.

如果您使用

explain (analyze true, verbose true, buffers true) select ....

您可以看到有关实际情况的更多详细信息

you can see a bit more details about what is really going on.

顺便说一句：您不应该使用 text 作为列名，因为Postgres中的数据类型也是如此（因此是保留字）。

Btw: you shouldn't use text as a column name, as that is also a datatype in Postgres (and thus a reserved word).

这篇关于PostgreSQL为什么不在小表上使用索引？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！