SELECT EXISTS vs. LIMIT 1 [英] SELECT EXISTS vs. LIMIT 1

问题描述

我看到 SELECT EXISTS 经常使用:

    if db.query("""
        SELECT EXISTS (
            SELECT 1 FROM checkout
            WHERE checkout_id = %s
        )
    """ % checkout_id).getresult()[0][0] == 't':

对比我更喜欢:

    if db.query("""
        SELECT 1 FROM checkout
        WHERE checkout_id = %s
        LIMIT 1
    """ % checkout_id).getresult():

哪个更受欢迎，为什么?

Which one is preferred and why?

附言我正在使用 Python 和 PosgreSQL.

P.S. i am using Python and PosgreSQL.

cert=> explain SELECT EXISTS (SELECT 1 FROM checkout WHERE checkout_id = 3);
                                      QUERY PLAN                                      
--------------------------------------------------------------------------------------
 Result  (cost=4.03..4.03 rows=1 width=0)
   InitPlan
     ->  Index Scan using checkout_pkey on checkout  (cost=0.00..4.03 rows=1 width=0)
           Index Cond: (checkout_id = 3)
(4 rows)

cert=> explain SELECT 1 FROM checkout WHERE checkout_id = 3 limit 1;
                                     QUERY PLAN                                     
------------------------------------------------------------------------------------
 Limit  (cost=0.00..4.03 rows=1 width=0)
   ->  Index Scan using checkout_pkey on checkout  (cost=0.00..4.03 rows=1 width=0)
         Index Cond: (checkout_id = 3)
(3 rows)

我的观点是，为什么从结果中获取一行并检查它的第一列是否为真，如果我可以检查是否有任何行，意思相同?

My point is, why getting a row from the result and check if it's first column is true, if i can just check if there are any rows at all, meaning the same?

推荐答案

PostgreSQL 似乎足够聪明，可以像您在执行计划中清楚地看到的那样对待这两个语句.

PostgreSQL seems to clever enough to treat both statements alike as you can clearly see in your execution plans.

我对本地表的测试有 ~150000 行和 ~100 行被条件选中，也显示了相同的行为

My tests with a local table with ~150000 rows and ~100 selected out of that by the condition also show the same behaviour

底线是:您使用哪一个并不重要，但您应该意识到其他 DBMS 可能不会以相同的方式运行.

The bottomline is: it doesn't really matter which one you use, but you should be aware that other DBMS might not behave in the same way.

这篇关于SELECT EXISTS vs. LIMIT 1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！