Query not properly using indices

[Manuel Diez Silva]

Hello everyone,

I'm currently facing some challenges on column indexation and I would love to get some help. I have trimmed down the problem a lot to illustrate the issue better and it goes like this:

I have a query which has to retrieve all the rows from a table based on some foreign key ids from another table (foreign keys being filtered by one condition). This is the query:

SELECT *
FROM monitor
WHERE
zone_id IN (SELECT id FROM zone WHERE main = TRUE);

This query does not use the index for the column zone_id and it takes a very long time, whereas if I do this other query:

SELECT *
FROM monitor
WHERE
zone_id IN ('1','2','3','4','5','6','9');

It indexes the column properly and it's basically instant.

The array used is the result of the former subquery.
I have removed a lot more stuff from the query to make my point simpler, but tried this simpler scenario and got the same results.
Why is this happening and is there any way I can dynamically select the filter array?

Thanks a lot in advance!
Thanks in