On Fri, Jan 22, 2010 at 1:42 AM, Richard Huxton
<dev@archonet.com> wrote:
On 21/01/10 22:15, Tory M Blue wrote:
· Data distribution = In the 98mill records, there are 7000 unique
makeid's, and 21mill unique UID's. About 41mill of the records have
tagged=true
· Time to execute the following query with indices on makeid and
tagged = 90-120 seconds. The planner uses the webid index and filters on
tagged and then rechecks the webid index
* SELECT COUNT(DISTINCT uid ) AS active_users FROM
pixelpool.userstats WHERE makeid ='bmw-ferman' AND tagged =true*
· Time to execute the the same query with a combined index on makeid
and tagged = 60-100 seconds. The planner uses the combined index and then
filters tagged.
Two things:
1. You have got the combined index on (makeid, tagged) and not (tagged, makeid) haven't you? Just checking.
Yes we do
2. If it's mostly tagged=true you are interested in you can always use a partial index: CREATE INDEX ... (makeid) WHERE tagged
This might be a win even if you need a second index with WHERE NOT tagged.
Partial index doesn't seem to fit here due to the fact that there are 35-40% Marked True.
Didn't think about creating a second index for false, may give that a shot.
Also, either I've not had enough cofee yet, or a bitmap scan is an odd choice for only ~ 13000 rows out of 100 million.
* " -> Bitmap Index Scan on
idx_retargetuserstats_makeidtag (cost=0.00..360.20 rows=13175 width=0)"*
* " Index Cond: ((makeid = 'b1mw-ferman'::text)
AND (tagged = true))"*
Otherwise, see what Craig said.
I'm assuming this isn't the query that is CPU bound for a long time. Unless your table is horribly bloated, there's no reason for that judging by this plan.
It is, but not always, only when there are 10K more matches. And the explain unfortunately is sometimes way high or way low, so the expalin is hit and miss.
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.
Thanks
Tory