How to speed up query with DISTINCT in PostgreSQL?

Question

PostgreSQL

Hello comrades! Please help me figure it out.
There is a pretty simple script. It looks like this:

SELECT DISTINCT("CITY" || ' | '  || "AREA" || ' | ' || "REGION") AS LOCATION FROM youtube

The youtube table that is used in the query has 25 million records. Every 5 minutes the table is updated with new data. This script is processed for a very long time (~50 seconds).
EXPLAIN ANALYZE returns the following:

I am trying to speed up a query. Added an index like this:

CREATE INDEX location_index ON youtube ("CITY", "AREA", "REGION")

After adding the index, the query did not speed up. Perhaps you need to explicitly specify in the query itself that the index is used. How to do it in PostgreSQL? I know that, for example, MySQL uses FORCE INDEX for this . Is there any point in the index or partitioning times in the query is not used WHERE .

Answer 1

The Postgresql scheduler does not currently have a loose index scan. Therefore, the planner, quite rightly from its point of view, chooses not to use any indexes.
Therefore, if it is distinct that is needed for a not very selective field, then it is more efficient to explain to the planner what they want from it with their hands: https://wiki.postgresql.org/wiki/Loose_indexscan
You rather need to change the data storage scheme.
No way. The PostgreSQL community deliberately refuses to add any scheduler hints, preferring to report scheduler errors as bugs and fix them if possible.

Answer 2

A composite index on fields is not the same as an index on the concatenated value of those fields. The corresponding index should be created like this
But it is even better to reconsider the approach to the structure of the table and queries.