Re: Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL

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