Tom, your suggestions have had an interesting and unexpected result......remember
the original query 'select count from error_log where site_id=39 and
host_id=99'....you suggested that I change this to add the ::int8 and enable use of
the index. I did this and the EXPLAIN results definitely show the difference.
However, with the new query, depending on the values of site_id and host_id,
execution time varies from .002 to 1.617 seconds!
Very odd, no? I wonder why the drastic (80000% variation!) difference in execution
times? I've vacuum'ed and vacuum anaylzed the table, too.
EXPLAIN
monitoring=# explain select count from error_log where site_id=39::int8 and
host_id=99::int8;
NOTICE: QUERY PLAN:
Subquery Scan error_log (cost=0.00..4.90 rows=1 width=16)
-> Aggregate (cost=0.00..4.90 rows=1 width=16)
-> Group (cost=0.00..4.90 rows=1 width=16)
-> Index Scan using log_siteid_hostid_index on log (cost=0.00..4.90
rows=1 width=16)
monitoring=# explain select count from error_log where site_id=14::int8 and
host_id=41::int8;
NOTICE: QUERY PLAN:
Subquery Scan error_log (cost=0.00..4.90 rows=1 width=16)
-> Aggregate (cost=0.00..4.90 rows=1 width=16)
-> Group (cost=0.00..4.90 rows=1 width=16)
-> Index Scan using log_siteid_hostid_index on log (cost=0.00..4.90
rows=1 width=16)
EXPLAIN
monitoring=#
2001-05-08 14:21:17 DEBUG: query: select count from error_log where
site_id=39::int8 and host_id=99::int8;
QUERY STATISTICS
! system usage stats:
! 0.002321 elapsed 0.000000 user 0.000000 system sec
2001-05-08 14:21:21 DEBUG: query: select count from error_log where
site_id=14::int8 and host_id=41::int8;
QUERY STATISTICS
! system usage stats:
! 1.617670 elapsed 0.810000 user 0.810000 system sec
