[PERFORM] limit + order by is slow if no rows in result set

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

Re: [PERFORM] limit + order by is slow if no rows in result set

Brian Cox wrote: 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

Re: [PERFORM] limit + order by is slow if no rows in result set

Hi Heikki, Thanks for your response. Please run EXPLAIN ANALYZE on both queries, and send back the results. [EMAIL PROTECTED] 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

Re: [PERFORM] limit + order by is slow if no rows in result set

Brian Cox [EMAIL PROTECTED] writes: Please run EXPLAIN ANALYZE on both queries, and send back the results. [ results... ] The reason the hash plan is fairly fast is that the hash join code has a special hack: if it reads the inner relation and finds it contains no rows, it knows there can be no