CODEWITHSUNDEEP
sqlpostgresqlindexing
albert
albert

Reputation: 1348

Postgres Indexes isn't being utilized

New to Postgres, and not very familiar with how RDBMS work in general. I read that, in certain cases, adding an index to a table speeds up query performance for databases. I tried it out with a table and did so (Postgres v11.2):

CREATE TABLE testtable(
    idString text,
    comment text
);


INSERT INTO 
    testtable(idString, comment)
VALUES
    ('1:2', 'some text'),
    ('12:2', 'blah'),
    ('2:2', 'other text'),
    ('1:3', 'blah'),
    ('33:2', 'blah');


CREATE INDEX myindex ON testtable(idString asc);

The guide I was reading said that, without an index, the database usually does a "sequential scan" of all entries until the query is found, but with an index, it does an "index scan". The guide says to see the query plan using "EXPLAIN", so I do:

EXPLAIN SELECT * FROM testtable WHERE myid = '1:3';

The output, however still seems to be a sequential scan:

                        QUERY PLAN
----------------------------------------------------------
 Seq Scan on testtable  (cost=0.00..1.07 rows=1 width=64)
   Filter: (myid = '1:3'::text)
(2 rows)

I've checked using pgAdmin and see that myindex does exist, but I'm unsure why the database isn't using it? Is there something else that I'm missing/haven't done?

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

Databases take many factors into consideration when deciding to use an index.

Your query is:

SELECT *
FROM testtable
WHERE myid = '1:3';

There are basically two reasonable approaches:

The first is to scan the data, and apply the WHERE clause to each row.

The second is to lookup the value in the index and then fetch the rest of the data.

Which is cheaper? In your case, the first is cheaper. Why? Only one page needs to be moved from tertiary storage into memory. Scanning the page -- after doing all the work of loading it -- is pretty cheap.

Using the index requires loading two pages, one for the index and one for the data.

Although database optimization is complicated, this is a simple example to give you a flavor of the different methods used in optimization and the trade-offs.

Upvotes: 3

Related Questions