Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 843DD9FB32A for ; Mon, 12 Feb 2007 17:51:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 22606-07 for ; Mon, 12 Feb 2007 17:50:59 -0400 (AST) X-Greylist: delayed 00:12:04.346236 by SQLgrey-1.7.4 Received: from smtp2.ca.com (smtp2.ca.com [141.202.248.13]) by postgresql.org (Postfix) with ESMTP id ABCD69FB387 for ; Mon, 12 Feb 2007 17:51:02 -0400 (AST) Received: from [138.42.244.37] ([138.42.244.37]) by smtp2.ca.com with Microsoft SMTPSVC(6.0.3790.1830); Mon, 12 Feb 2007 16:38:56 -0500 Message-ID: <45D0DDED.1070400@ca.com> Date: Mon, 12 Feb 2007 13:36:45 -0800 From: Brian Cox User-Agent: Thunderbird 1.5.0.7 (X11/20060909) MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: limit + order by is slow if no rows in result set Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 12 Feb 2007 21:38:56.0241 (UTC) FILETIME=[32A81E10:01C74EEE] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=BAYES_50, SPF_PASS X-Spam-Level: X-Archive-Number: 200702/118 X-Sequence-Number: 23172 There are 1.9M rows in ts_defects and indexes on b.ts_id (primary key) d.ts_biz_event_id and d.ts_occur_date. Both queries below return 0 rows. The 1st runs fast and the 2nd > 400x slower. The 2nd query differs from the 1st only by the addition of "limit 1". Why the big difference in performance? Thanks, Brian [bcox@athena jsp]$ time PGPASSWORD=**** psql -U admin -d cemdb -h 192.168.1.30 -c 'select * from ts_defects d join ts_biz_events b on b.ts_id = d.ts_biz_event_id where b.ts_status=3 order by d.ts_occur_date desc;' (column list deleted) -------+--------------+--------------+---------------+---------------------------+----------------+----------------+------------+------------------------+------------------+-----------------+---------------+------------------+---------------+-----------------+-------------------+---------------------+---------------------+---------------------+--------------------+----------------+--------------------+----------------------+--------------------+----------------------+-----------------------+----------------+----------------------+---------------+-----------------+------------------+--------------+----------------+-------+--------------+---------------+---------------------------+------------------+---------+--------------------+---------------+-----------------+------------------+---------------+----------------------+---------------------+--------------------+-----------+---------------------+----------+---------------+--------------+------------------+-------------+-------- -----+--------------+--------------+---------------- (0 rows) real 0m0.022s user 0m0.003s sys 0m0.003s [bcox@athena jsp]$ time PGPASSWORD=**** psql -U admin -d cemdb -h 192.168.1.30 -c 'select * from ts_defects d join ts_biz_events b on b.ts_id = d.ts_biz_event_id where b.ts_status=3 order by d.ts_occur_date desc limit 1;' (column list deleted) -------+--------------+--------------+---------------+---------------------------+----------------+----------------+------------+------------------------+------------------+-----------------+---------------+------------------+---------------+-----------------+-------------------+---------------------+---------------------+---------------------+--------------------+----------------+--------------------+----------------------+--------------------+----------------------+-----------------------+----------------+----------------------+---------------+-----------------+------------------+--------------+----------------+-------+--------------+---------------+---------------------------+------------------+---------+--------------------+---------------+-----------------+------------------+---------------+----------------------+---------------------+--------------------+-----------+---------------------+----------+---------------+--------------+------------------+-------------+-------- -----+--------------+--------------+---------------- (0 rows) real 0m9.410s user 0m0.005s sys 0m0.002s