On Fri, Jan 22, 2010 at 10:26 AM, Matthew Wakeling <matthew@flymine.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
differenceis.
>
> 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"
FAST
"Explain
"explain SELECT a.makeid, COUNT(DISTINCT CASE WHEN tagged=true
THEN a.uid END)
"AS active_users,
"COUNT(DISTINCT CASE WHEN tagged=false THEN a.uid END) as unassociated
"FROM pixelpool.userstats a
"WHERE makeid ='gmps-oden' GROUP BY a.makeid
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"
Thanks again
Tory