Search code examples
sqlpostgresqlpostgresql-14

Unused index in PostgreSQL

I'm learning indexing in PostgreSQL now. I started trying to create my index and analyzing how it will affect execution time. I created some tables with such columns:

enter image description here

enter image description here

also, I filled them with data. After that I created my custom index:

create index events_organizer_id_index on events(organizer_ID);

and executed this command (events table contains 148 rows):

explain analyse select * from events where events.organizer_ID = 4;

I was surprised that the search was executed without my index and I got this result:

enter image description here

As far as I know, if my index was used in search there would be the text like "Index scan on events". So, can someone explain or give references to sites, please, how to use indexes effectively and where should I use them to see differences?

Solution

  • From "Rows removed by filter: 125" I see there are too few rows in the events table. Just add couple of thousands rows and give it another go

    from the docs

    Use real data for experimentation. Using test data for setting up indexes will tell you what indexes you need for the test data, but that is all.

    It is especially fatal to use very small test data sets. While selecting 1000 out of 100000 rows could be a candidate for an index, selecting 1 out of 100 rows will hardly be, because the 100 rows probably fit within a single disk page, and there is no plan that can beat sequentially fetching 1 disk page.

    In most cases, when database using an index it gets only address where the row is located. It contains data block_id and the offset because there might be many rows in one block of 4 or 8 Kb.

    So, the database first searches index for the block adress, then it looks for the block on disk, reads it and parses the line you need.

    When there are too few rows they fit into one on in couple of data blocks which makes it easier and quicker for DB to read whole table without using index at all.