limit + order by is slow if no rows in result set
От | Brian Cox |
---|---|
Тема | limit + order by is slow if no rows in result set |
Дата | |
Msg-id | 45D0DDED.1070400@ca.com обсуждение исходный текст |
Ответы |
Re: limit + order by is slow if no rows in result set
|
Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: