On Fri, Jan 22, 2010 at 11:06 AM, Tory M Blue <tmblue@xxxxxxxxx> wrote: > On Fri, Jan 22, 2010 at 10:26 AM, Matthew Wakeling <matthew@xxxxxxxxxxx> 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" -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
