On Fri, Jan 22, 2010 at 10:59 AM, Tory M Blue <tmb...@gmail.com> wrote: > On Thu, Jan 21, 2010 at 7:46 PM, Craig Ringer <cr...@postnewspapers.com.au> > wrote: >> > 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. >> >> What is the reporting query that takes 26 hours? You didn't seem to >> include it, or any query plan information for it (EXPLAIN or EXPLAIN >> ANALYZE results). > > It's this query, run 6000 times with a diff makeid's > > SELECT COUNT(DISTINCT uid ) AS active_users FROM pixelpool.userstats > WHERE makeid ='bmw-ferman' AND tagged =true
Any chance of trying this instead: select makeid, count(distinct uid) as active_users from pixelpool.userstats where tagged=true group by makeid And seeing how long it takes? If you're limiting the total number of makeids then you could add and makeid in (biglistofmakeidsgoeshere) Note that a partial index of create index xyz on pixelpool.userstats (makeid) where tagged; might help both the original and this query. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
