You probably don't want to hear this, but the best option to speed up
SELECT DISTINCT is to avoid
DISTINCT to begin with. In many cases (not all!) it can be avoided with better database-design or better queries.
GROUP BY is faster, because it takes a different code path.
In your particular case, it doesn't seem like you can get rid of
DISTINCT. But you could support the query with a specialized index if you have many queries of that kind:
CREATE INDEX foo ON events (project_id, "time", user_id);
user_id is only useful if you get index-only scans out of this. Follow the link for details. Would remove the expensive
Bitmap Heap Scan from your query plan, which consumes 90% of the query time.
EXPLAIN output tells me that the query has to condense 2,491 distinct users out of half a million matching rows. This won't become super-fast, no matter what you do, but it can be substantially faster.
If the time intervals in your queries are always the same, a
MATERIALIIZED VIEW folding
(project_id, <fixed time intervall>) would go a long way. No chance there with varying time intervals, though. Maybe you could at least fold users per hour or some other minimum time unit, and that would buy enough performance to warrant the considerable overhead.
Most probably, the predicates on
"time" should really be:
AND "time" >= '2015-01-11 8:00:00'
AND "time" < '2015-02-10 8:00:00';
time as identifier. It's a reserved word in standard SQL and a basic type in Postgres.