Without having looked at this in detail my first suggestion would be to
do away with those date_part indices. I have found that indexes with few
distinct values usually hurt more then help and the PG optimizer is not
always smart enough to ignore them and the BitmapAnd and scan for dates
seem like a waste since you can consolidate that information from the
get-go, e.g. could you rewrite your WHERE clause to be something like:
WHERE date_trunc('quarter', entry_date) = '2006-04-01' -- for 2nd
quarter of '06
or
WHERE entry_date >= '2006-04-01'
AND entry_date < '2006-07-01'
You could try an index on either the date_trunc or the entry_date
itself, as appropriate.
I assume your user_tracking table is being inserted onto on each visit,
so you may want to be very cautious with what indexes you have on it
anyway and pare those down.
BTW, you have a redundant "WHERE subscription_id > 0" in the outer
query, not that that affects much.
