Re: limit + order by is slow if no rows in result set - Mailing list pgsql-performance

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