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