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 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)

[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 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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to