我们如何在同一表上优化非子查询？

Question

下面的查询是使用NOT IN的子查询过滤结果，如何避免NOT IN？NOT IN还有其他选择吗？如何优化整个查询？

我看到了在lower(display_name)上添加索引的一个选项，但是在这里我可以做更好的或者进一步的优化吗？

SELECT  table_name.* FROM table_name 
WHERE (
        table_name.id IN (
        SELECT distinct on (name, mode, formulation, strength, generic_name) id FROM table_name 
        where lower(display_name) 
          NOT IN(SELECT lower(display_name) FROM table_name WHERE table_name.user_id IS NULL)
      ) 
ORDER BY table_name.created_at DESC LIMIT 100 OFFSET 0

查询计划

------------------------------------------------------------------------------------------------
 Limit  (cost=9277.38..9277.38 rows=1 width=296)
   ->  Sort  (cost=9277.38..9351.72 rows=29736 width=296)
         Sort Key: table_name.created_at DESC
         ->  Nested Loop  (cost=8015.41..9128.70 rows=29736 width=296)
               ->  HashAggregate  (cost=8015.12..8017.12 rows=200 width=4)
                     Group Key: table_name_1.id
                     ->  Unique  (cost=7486.65..7932.69 rows=6594 width=93)
                           ->  Sort  (cost=7486.65..7560.99 rows=29736 width=93)
                                 Sort Key: table_name_1.name, table_name_1.mode, table_name_1.formulation, table_name_1.strength, table_name_1.generic_name
                                 ->  Seq Scan on table_name table_name_1  (cost=2286.19..5277.28 rows=29736 width=93)
                                       Filter: (NOT (hashed SubPlan 1))
                                       SubPlan 1
                                         ->  Bitmap Heap Scan on table_name table_name_2  (cost=106.16..2272.68 rows=5402 width=32)
                                               Recheck Cond: (user_id IS NULL)
                                               ->  Bitmap Index Scan on index_table_name_on_user_id  (cost=0.00..104.81 rows=5402 width=0)
                                                     Index Cond: (user_id IS NULL)
               ->  Index Scan using table_name_pkey on table_name  (cost=0.29..6.61 rows=1 width=296)
                     Index Cond: (id = table_name_1.id)

我想做的是table_name是有两份清单的药物

1. 系统范围的药物，例如由管理员添加的药物，在本例中，我们将user_id保持为空。
2. 用户专用药物是指由具有user_id值

的用户添加的。

我在搜索中返回两个结果: 1.管理毒品2.所有使用者都喜欢

管理列表

admin_list = select * from table_name user_id IS NULL and LOWER(table_name.name) like %cal%

所有需要跳过以下查询的管理列表中的名称

SELECT lower(display_name) FROM table_name WHERE table_name.user_id IS NULL

所有用户都使用药物，但跳过/过滤出基于display_name的管理列表，如

user_list = SELECT  table_name.* FROM table_name 
WHERE (
        table_name.id IN (
        SELECT distinct on (name, mode, formulation, strength, generic_name) id FROM table_name 
        where lower(display_name) 
          NOT IN(SELECT lower(display_name) FROM table_name WHERE table_name.user_id IS NULL)
      ) 
AND LOWER(table_name.name) LIKE '%cal%'
ORDER BY table_name.created_at DESC LIMIT 100 OFFSET 0

目标是有两个不同的列表admin_list和user_list，其中user_list不应该包含任何管理列表display_name

Answer 1

通常情况下，如果速度快于不存在，就不会存在。

SELECT t1.* 
FROM table_name t1
WHERE table_name.id IN (SELECT distinct on (t2.name, t2.mode, t2.formulation, t2.strength, t2.generic_name) t2.id 
                        FROM table_name t2
                        where not exists (select * 
                                          FROM table_name t3
                                          WHERE t3.user_id IS NULL
                                            and t2.lower(display_name) = t3.lower(display_name) )
                       ) 
ORDER BY table_name.created_at DESC 
LIMIT 100 OFFSET 0

一个关于table_name ( (lower(display_name)) ) where user_is null的指数应该会更好地改善这一点。

请注意，没有订单的distinct on ()通常不是一个好主意。

Answer 2

您似乎希望每个name, mode, formulation, strength, generic_name组合都有一个id，而display_name没有null user_id。

如果这是正确的，我相信有更简单的方法来编写查询：

select t.*
from (select distinct on (name, mode, formulation, strength, generic_name) t.* 
      from (select t.*,
                   count(*) filter (where user_id is null) over (partition by lower(display_name)) as cnt
            from table_name
           ) t
      where cnt = 0
      order by name, mode, formulation, strength, generic_name, created_at desc
     ) t
order by created_at desc
limit 100 offset 0