无缺式子查询Postgresql

发布于 2025-01-20 10:09:19 字数 1126 浏览 2 评论 0原文

您好，我有这个查询：

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

它在 323 毫秒内返回：

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

我将它用作另一个查询中的子查询 in 子句：

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 )

检索结果大约需要 20 秒；

但是当我尝试使用 where 子句进行相同的查询时：

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

结果只需要大约 300 毫秒。

看起来当 WHERE 子句中有子查询时，它会使整个表被扫描。

有什么方法可以“模拟”按键的硬编码吗？

原文

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?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

舟遥客 2025-01-27 10:09:20

正如Erwin和a_horse_with_no_name指出的那样，您的问题缺乏详细的详细信息来提出确定的解决方案）

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)

更快的解决方案的一个明显的候选人是使用CTE和连接（但是从您的查询中输出的绝对最低限度，这只是一个有根据的猜测。

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.

回复收藏 0 原文

~没有更多了~