Message-ID: <45D10527.8000206@ca.com>
Date: Mon, 12 Feb 2007 16:24:07 -0800
From: Brian Cox <brian.cox@ca.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060909)
MIME-Version: 1.0
To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: 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

Hi Heikki,

Thanks for your response.

> Please run EXPLAIN ANALYZE on both queries, and send back the results.

[bcox@athena jsp]$ PGPASSWORD=quality psql -U admin -d cemdb -h 
192.168.1.30 -c 'explain analyze 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;'
 
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
  Sort  (cost=160400.01..160646.91 rows=98762 width=2715) (actual 
time=0.303..0.303 rows=0 loops=1)
    Sort Key: d.ts_occur_date
    ->  Hash Join  (cost=33.20..82567.14 rows=98762 width=2715) (actual 
time=0.218..0.218 rows=0 loops=1)
          Hash Cond: ("outer".ts_biz_event_id = "inner".ts_id)
          ->  Seq Scan on ts_defects d  (cost=0.00..71882.88 
rows=1932688 width=1545) (actual time=0.022..0.022 rows=1 loops=1)
          ->  Hash  (cost=33.04..33.04 rows=65 width=1170) (actual 
time=0.135..0.135 rows=0 loops=1)
                ->  Bitmap Heap Scan on ts_biz_events b 
(cost=2.23..33.04 rows=65 width=1170) (actual time=0.132..0.132 rows=0 
loops=1)
                      Recheck Cond: (ts_status = 3)
                      ->  Bitmap Index Scan on ts_biz_events_statusindex 
  (cost=0.00..2.23 rows=65 width=0) (actual time=0.054..0.054 rows=61 
loops=1)
                            Index Cond: (ts_status = 3)
  Total runtime: 0.586 ms
(11 rows)

[bcox@athena jsp]$ PGPASSWORD=quality psql -U admin -d cemdb -h 
192.168.1.30 -c 'explain analyze 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;'
    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.00..87.37 rows=1 width=2715) (actual 
time=17999.482..17999.482 rows=0 loops=1)
    ->  Nested Loop  (cost=0.00..8628543.77 rows=98762 width=2715) 
(actual time=17999.476..17999.476 rows=0 loops=1)
          ->  Index Scan Backward using ts_defects_dateindex on 
ts_defects d  (cost=0.00..227675.97 rows=1932688 width=1545) (actual 
time=0.047..3814.923 rows=1932303 loops=1)
          ->  Index Scan using ts_biz_events_pkey on ts_biz_events b 
(cost=0.00..4.33 rows=1 width=1170) (actual time=0.005..0.005 rows=0 
loops=1932303)
                Index Cond: (b.ts_id = "outer".ts_biz_event_id)
                Filter: (ts_status = 3)
  Total runtime: 17999.751 ms
(7 rows)

> Also, what indexes are there on the tables involved?

I tried to mention the relevant indexes in my original posting, but 
omitted one; here's a list of all indexes:

ts_defects: ts_id, ts_occur_date, ts_defect_def_id, ts_biz_event_id, 
ts_trancomp_id, ts_transet_incarnation_id, ts_transet_id, 
ts_tranunit_id, ts_user_incarnation_id, ts_user_id

ts_biz_events: ts_id, ts_defect_def_id, ts_status

Thanks,
Brian