On Fri, Jan 22, 2010 at 10:59 AM, Tory M Blue <tmblue@gmail.com> wrote:
> On Thu, Jan 21, 2010 at 7:46 PM, Craig Ringer <craig@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.