Why is PostgreSQL not using my indexes on a small table?

Question

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 )

I make the following selects:

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

The explanation of execution plan looks like this:

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?

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?

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.

If efficiency doesn't matter, only simplicity, why do you care whether it's using an index or not? Indexes are performance optimisations that don't affect semantics - well, with the exception of implementation details like Pg's UNIQUE indexes as implementation for UNIQUE constraints. — Craig Ringer
– Craig Ringer, Commented Mar 18, 2013 at 11:06
Craig, "efficiency doesn't matter" relates only to the way of inserting the dummy rows, sorry for misleading phrase. — KutaBeach
– KutaBeach, Commented Mar 18, 2013 at 12:17

user330315 · Accepted Answer · 2013-03-18 11:09:36Z

12

Maybe, the reason is that I have too few rows in my table?

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.

If you use

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

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

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

answered Mar 18, 2013 at 11:09

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

Craig Ringer Over a year ago

@KutaBeach Note that you can force index scans for the purpose of testing whether it's possible to use an index for a particular query by using the enable_ parameters as documented in the manual. These parameters are for testing purposes only and should not generally be used in production.

Jakub Kania · Accepted Answer · 2013-03-18 11:01:38Z

2

The example you have found is for DB2, in pg you can use generate_series to do it. For example like this:

INSERT INTO index_test(data,last_modified,value,item_type) 
SELECT
    md5(random()::text),now(),floor(random()*100),md5(random()::text) 
    FROM generate_series(1,1000);
SELECT max(value) from index_test;

http://sqlfiddle.com/#!12/52641/3

The second query in above fiddle should use index only scan.

answered Mar 18, 2013 at 11:01

Jakub Kania

16.6k2 gold badges44 silver badges51 bronze badges

Collectives™ on Stack Overflow

Why is PostgreSQL not using my indexes on a small table?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related