CODEWITHSUNDEEP
postgresqlindexing
Малъ Скрылевъ
Малъ Скрылевъ

Reputation: 16507

Postgres don't pickup an index on text/varchar columns

I'm using PostgreSQL. I've created an index for the specified column:

# CREATE INDEX sampleidx ON fake_organizations (objectable_type) ;
CREATE INDEX

The field objectable_type has type of varchar. When I selects records by the field it uses SeqScan with explicit type cast, and don't pick up the index.

# explain analyze select * from fake_organizations where objectable_type = 'Lot' LIMIT 10;
QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.00..2.01 rows=10 width=493) (actual time=0.001..0.001 rows=0 loops=1)
->  Seq Scan on fake_organizations  (cost=0.00..11.88 rows=59 width=493) (actual time=0.000..0.000 rows=0 loops=1)
     Filter: ((objectable_type)::text = 'Lot'::text)
Planning time: 0.765 ms
Execution time: 0.027 ms
(5 строк)

The same case but without a typecast is happening when the column has type of text.

So the question is: why?

Upvotes: 0

Views: 1172

Answers (1)

Paweł Dyl
Paweł Dyl

Reputation: 9143

Seems that your table is empty or almost empty. There is no need to use index. It's easier to scan. You don't use complex algorithms when you have to sort 2 objects. Postgres don't use tree traversal algorithm for the same reason.

Upvotes: 1

Related Questions