The issues we are seeing besides just saying the reports take over 26 hours, is that the query seems to be CPU bound. Meaning that the query consumes an entire CPU and quite often it is sitting with 65%-90% WAIT. Now this is not iowait, the disks are fine, 5000-6000tps, 700K reads etc with maybe 10-13% iowait.
However much of the time that I see the CPU at 65%-90% Wait, there is very little disk access, so it's not the disk subsystem (in my opinion). I've also moved CPU's around and the sql seems to stall regardless of what CPU the job has been provided with. Memory I pulled memory to test and again, other than this data set consuming 10gigs of data, 700K free (will add more memory), but since the disks are not a bottleneck and I don't appear to be swapping, I keep coming back to the data or sql.
I'm providing the data that I think is requested when a performance issue is observed. There is an autovac running, the queries are run on static data so
INSERTS/UPDATES/DELETES The query seems to have gotten slower as the data set grew.
Redhat
Postgres 8.3.4
8 cpu box
10gig of ram
Number of rows in the table= 100million
· Size of table including indices =21GB
· Time to create a combined index on 2 columns (tagged boolean , makeid text) = more than 1 hr 30 minutes
· Data distribution = In the 98mill records, there are 7000 unique makeid's, and 21mill unique UID's. About 41mill of the records have tagged=true
· Time to execute the following query with indices on makeid and tagged = 90-120 seconds. The planner uses the webid index and filters on tagged and then rechecks the webid index
SELECT COUNT(DISTINCT uid ) AS active_users FROM pixelpool.userstats WHERE makeid ='bmw-ferman' AND tagged =true
· Time to execute the the same query with a combined index on makeid and tagged = 60-100 seconds. The planner uses the combined index and then filters tagged.
SELECT COUNT(DISTINCT uid ) AS active_users FROM pixelpool.userstats WHERE makeid ='bmw-ferman' AND tagged =true
Plan
"Aggregate (cost=49467.00..49467.01 rows=1 width=8)"
" -> Bitmap Heap Scan on userstats (cost=363.49..49434.06 rows=13175 width=8)"
" Recheck Cond: (makeid = 'b1mw-ferman'::text)"
" Filter: tagged"
" -> Bitmap Index Scan on idx_retargetuserstats_makeidtag (cost=0.00..360.20 rows=13175 width=0)"
" Index Cond: ((makeid = 'b1mw-ferman'::text) AND (tagged = true))"
Any assistance would be appreciated, don't worry about slapping me around I need to figure this out. Otherwise I'm buying new hardware where it may not be required.
Thanks
Tory