From: Brian Cox <brian.cox@ca.com>
To: pgsql-performance@postgresql.org
Subject: limit + order by is slow if no rows in result set
Date: Mon, 12 Feb 2007 13:36:45 -0800
Message-ID: <45D0DDED.1070400@ca.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: brian.cox@ca.com
Subject: Re: limit + order by is slow if no rows in result set
In-Reply-To: <45D0DDED.1070400@ca.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox