Hey folks. I found out my wife had a meeting this evening and so I'll have to stay home with our daughter. Sorry about the late notice.
But here is something to chew on:
Just upgraded from 7.3.6 to 7.4.2 this past weekend. We had a query that used to take 20 mins to run......It now takes.....45 secs! Of course we re-wrote what we had originally done because of the way 7.3.6 handled indexes. We used the simple query below to spot the problem:
7.3.6 queries:
this query worked great. It used the index on machine_id and date_est:
select *
from cdm.cdm_partnerlog
where
machine_id=100838873
and date_est='2004-04-05'
--explain
Index Scan using cdm_partnerlog_machine_id on cdm_partnerlog (cost=0.00..1106.38 rows=3 width=76) (actual time=0.39..0.39 rows=0 loops=1)
Index Cond: (machine_id = 100838873)
Filter: (date_est = '2004-04-05'::date)
Total runtime: 0.44 msec
---
This query first did a scan of the entire table using the date_est field and then after it pull the result set then filtered on the machine_id field. This would take a stink load of time due to the number of records:
select *
from cdm.cdm_partnerlog
where
machine_id=100838873
and date_est>='2004-04-05'
--explain
Index Scan using cdm_partnerlog_date_est on cdm_partnerlog (cost=0.00..955.45 rows=1 width=76) (actual time=3274.86..3274.86 rows=0 loops=1)
Index Cond: (date_est >= '2004-04-05'::date)
Filter: (machine_id = 100838873)
Total runtime: 3274.93 msec
---
Once we updated to 7.4.2, the query below:
select *
from cdm.cdm_partnerlog
where
machine_id=100838873
and date_est>='2004-04-05'
Index Scan using cdm_partnerlog_machine_id on cdm_partnerlog (cost=0.00..315615.91 rows=62983 width=656) (actual time=0.149..0.149 rows=0 loops=1)
Index Cond: (machine_id = 100838873)
Filter: (date_est >= '2004-04-05'::date)
Total runtime: 0.243 ms
SWEEET!
Patrick Hatcher