How do I know if any index is used in a query | PostgreSQL 11?

Question

I am little bit confused and need some advice. I use PostgreSQL 11 database. I have such pretty simple sql statement:

SELECT DISTINCT "CITY", "AREA", "REGION"
    FROM youtube
WHERE
    "CITY" IS NOT NULL
AND
    "AREA" IS NOT NULL
AND
    "REGION" IS NOT NULL

youtube table which I use in sql statement has 25 million records. I think for thats why query takes 15-17 seconds to complete. For web project where I use that query it's too long. I'm trying to speed up the request.

I create such index for youtube table:

CREATE INDEX youtube_location_idx ON public.youtube USING btree ("CITY", "AREA", "REGION");

After this step I run query again but it takes the same time to complete. It seems like query don't use index. How do I know if any index is used in a query?

EXPLAIN ANALYZE return:

Answer 1

There is four types of scan that I know in PostgreSQL.

Sequential scan: Not uses index.

Index scan: Searches on index and then table.

Index only scan: Searches only on index, doesn't scan on actual table.

Bitmap heap scan: Between index scan and sequential scan.

Third row of your result (seq scan) shows that it scans whole table sequentially. So you are not using index.