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

On Fri, Jan 22, 2010 at 11:06 AM, Tory M Blue <tmblue(at)gmail(dot)com> wrote:
> On Fri, Jan 22, 2010 at 10:26 AM, Matthew Wakeling <matthew(at)flymine(dot)org> wrote:
>>
>> On Fri, 22 Jan 2010, Tory M Blue wrote:
>>>
>>> But the same sql that returns maybe 500 rows is pretty fast, it's the return
>>> of 10K+ rows that seems to stall and is CPU Bound.
>>
>> Okay, so you have two differing cases. Show us the EXPLAIN ANALYSE for both of them, and we will see what the difference is.
>>
>> Matthew
>
> Okay, understood
>
> Here is the explain plan for the query. Actual rows that the query
> returns is 6369
>
> SLOW
>
> This is for this SQL
>   SELECT COUNT(distinct uid )  AS active_users FROM
> pixelpool.userstats    WHERE makeid =
> 'gmps-armen-chevy' and tagged=true
>
> Explain
> "Aggregate  (cost=118883.96..118883.97 rows=1 width=8)"
> "  ->  Bitmap Heap Scan on userstats  (cost=797.69..118850.46
> rows=13399 width=8)"
> "        Recheck Cond: (makeid = 'gmps-armen-chevy'::text)"
> "        Filter: tagged"
> "        ->  Bitmap Index Scan on idx_retargetuserstats_makeid
> (cost=0.00..794.34 rows=33276 width=0)"
> "              Index Cond: (makeid = 'gmps-armen-chevy'::text)"
>
>
> Explain Analyze
> "Aggregate  (cost=118883.96..118883.97 rows=1 width=8) (actual
> time=31219.376..31219.376 rows=1 loops=1)"
> "  ->  Bitmap Heap Scan on userstats  (cost=797.69..118850.46
> rows=13399 width=8) (actual time=281.604..31190.290 rows=19799
> loops=1)"
> "        Recheck Cond: (makeid = 'gmps-armen-chevy'::text)"
> "        Filter: tagged"
> "        ->  Bitmap Index Scan on idx_retargetuserstats_makeid
> (cost=0.00..794.34 rows=33276 width=0) (actual time=258.506..258.506
> rows=23242 loops=1)"
> "              Index Cond: (makeid = 'gmps-armen-chevy'::text)"
> "Total runtime: 31219.536 ms"

Sorry mis-pasted the FAST!!!

Explain
"GroupAggregate  (cost=802.66..119105.01 rows=1 width=23)"
"  ->  Bitmap Heap Scan on userstats a  (cost=802.66..118855.43
rows=33276 width=23)"
"        Recheck Cond: (makeid = 'gmps-oden'::text)"
"        ->  Bitmap Index Scan on idx_retargetuserstats_makeid
(cost=0.00..794.34 rows=33276 width=0)"
"              Index Cond: (makeid = 'gmps-oden'::text)"

Explain Analyze
"GroupAggregate  (cost=802.66..119105.01 rows=1 width=23) (actual
time=3813.550..3813.551 rows=1 loops=1)"
"  ->  Bitmap Heap Scan on userstats a  (cost=802.66..118855.43
rows=33276 width=23) (actual time=55.400..3807.908 rows=2606 loops=1)"
"        Recheck Cond: (makeid = 'gmps-oden'::text)"
"        ->  Bitmap Index Scan on idx_retargetuserstats_makeid
(cost=0.00..794.34 rows=33276 width=0) (actual time=51.748..51.748
rows=2677 loops=1)"
"              Index Cond: (makeid = 'gmps-oden'::text)"
"Total runtime: 3813.626 ms"