sql—如何优化此查询的执行时间

oaxa6hgo 于 2021-08-13 发布在 Java

关注(0)|答案(1)|浏览(408)

我有以下疑问：

SELECT "factures"."id" 
FROM   "factures" 
WHERE  ( "factures"."id" NOT IN (SELECT DISTINCT( "echeances"."facture_id" ) 
                                 FROM   "echeances" 
                                 WHERE  "echeances"."type_decheance" IN ( 2, 3, 4, 5, 8, 9 ) 
                                        AND "echeances"."facture_id" IS NOT NULL 
                                 LIMIT  100000)) <----- removing this limit makes the query take enormous time
ORDER  BY "factures"."id" DESC

以下是限制为100000的解释分析：

Index Only Scan Backward using factures_id_pkey on factures  (cost=93516.76..211292.17 rows=530570 width=4) (actual time=1425.701..11466.759 rows=963698 loops=1)
  Filter: (NOT (hashed SubPlan 1))
  Rows Removed by Filter: 99997
  Heap Fetches: 1063695
  SubPlan 1
    ->  Limit  (cost=0.43..93266.34 rows=100000 width=4) (actual time=0.022..1229.925 rows=100000 loops=1)
          ->  Unique  (cost=0.43..264837.37 rows=283959 width=4) (actual time=0.022..1090.692 rows=100000 loops=1)
                ->  Index Scan using echeances__facture_id__idx on echeances  (cost=0.43..262883.29 rows=781631 width=4) (actual time=0.020..819.735 rows=100167 loops=1)
                      Index Cond: (facture_id IS NOT NULL)
"                      Filter: (type_decheance = ANY ('{2,3,4,5,8,9}'::integer[]))"
                      Rows Removed by Filter: 156995
Planning time: 0.249 ms
Execution time: 11960.423 ms

以下是无限制的解释：

->  Unique  (cost=0.43..264837.37 rows=283959 width=4)
Index Only Scan Backward using factures_id_pkey on factures  (cost=0.86..142233669403.15 rows=530570 width=4)
  Filter: (NOT (SubPlan 1))
  SubPlan 1
    ->  Materialize  (cost=0.43..267367.16 rows=283959 width=4)
                ->  Index Scan using echeances__facture_id__idx on echeances  (cost=0.43..262883.29 rows=781631 width=4)
                      Index Cond: (facture_id IS NOT NULL)
"                      Filter: (type_decheance = ANY ('{2,3,4,5,8,9}'::integer[]))"

这是模式

Table "factures"
id
Table "echeances"
id
facture_id (fk)
type_decheance (integer)

问题是“factures”和“echeances”表有大量的行，如果：
子查询中指定了limit，例如limit 100000，查询时间很快
在子查询中没有指定限制，这需要很多时间，我不得不在等待了15分钟之后停止它。
我们的目标是在合理的时间内无限制地运行这个查询。

sql postgresql query-performance psqlodbc

来源：https://stackoverflow.com/questions/61942130/how-to-optimize-the-execution-time-for-this-query

1条答案

按热度按时间

ttisahbt1#

切换到 NOT EXISTS :

SELECT f.id
FROM factures f
WHERE NOT EXISTS (SELECT 1
                  FROM echeances e
                  WHERE e.facture_id = f.id AND
                        e.type_decheance IN ( 2, 3, 4, 5, 8, 9 ) 
                 )
ORDER  BY f.id DESC;

注意，我删除了所有的双引号。不要引用你的标识符。它只会使查询更难写和读。
然后你需要一个索引 echeances(facture_id, type_decheance) . 这应该很快，因为每个facture id都可以通过简单的索引查找进行检查。

赞(0）回复(0）举报 2021-08-13

我来回答

sql—如何优化此查询的执行时间

1条答案

相关问题

热门标签

最新问答