inneficient subquery postgresql

Question

Hi I have this query:

select distinct r.fparams::json->>'uuid_level_2' as uuid_level_2
from jhft.run r 
where r.ts_run >= :ts_run 

which returns in 323ms:

49c954c3-9d57-4777-99cb-634e59393053
4e9f3aac-b9d0-422b-badf-171c24dac138
d68726a0-7176-4bd3-aac8-b796dab074a5

I'm using it as a subquery a in clause in this other query:

select distinct 
    r.fparams::json->>'uuid_level_2' as uuid_level_2, 
    first_value(r.fparams) over 
        (partition by r.fparams::json->>'uuid_level_2' order by r.id) as first_fparams
from jhft.run r
where r.fparams::json->>'uuid_level_2' in (
    select distinct r.fparams::json->>'uuid_level_2' as uuid_level_2 
    from jhft.run r 
    where r.ts_run >= :ts_run )

the results takes about 20 seconds to be retrieved;

BUT when I try to make the same query with the where clause as:

where r.fparams::json->>'uuid_level_2' in (
    'd68726a0-7176-4bd3-aac8-b796dab074a5',
    '49c954c3-9d57-4777-99cb-634e59393053',
    '4e9f3aac-b9d0-422b-badf-171c24dac138' )

the results takes just about 300 ms.

Looks like when there is a subquery in the WHERE clause it makes the whole table to be scanned.

any means to "simulate" the hard-coding of the keys?

Answer 1

An obvious candidate for a faster solution would be to use a CTE and a join (but as Erwin and a_horse_with_no_name pointed out, your question is lacking in detail to come up with a definitive solution):

WITH target AS (
    SELECT DISTINCT fparams::json->>'uuid_level_2' AS uuid_level_2 
    FROM jhft.run 
    WHERE ts_run >= :ts_run
)
SELECT DISTINCT 
    fparams::json->>'uuid_level_2' AS uuid_level_2, 
    first_value(fparams) OVER 
        (PARTITION BY fparams::json->>'uuid_level_2' ORDER BY id) AS first_fparams
FROM jhft.run
JOIN target USING (uuid_level_2)

However, without any EXPLAIN ANALYZE VERBOSE output from your query as an absolute minimum, this is only an educated guess.