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

From Scott Marlowe
Subject Re: Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL
Date
Msg-id dcc563d11001221038p7129b5a9m4d96c9211335d805@mail.gmail.com
Whole thread Raw
In response to Re: Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL  (Tory M Blue <tmblue@gmail.com>)
List pgsql-performance
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.

pgsql-performance by date:

Previous
From: Matthew Wakeling
Date:
Subject: Re: Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL
Next
From: Tory M Blue
Date:
Subject: Re: Data Set Growth causing 26+hour runtime, on what we believe to be very simple SQL