Untitled

How to optimize sql query? Seems distinct is very slow?

select count(DISTINCT userid)

from users

where date_trunc('month',login_date)=date_trunc('month','2012-01-12'::date)

       

CREATE OR REPLACE FUNCTION my_date_trunc_month(some_date DATE)

  RETURNS DATE

AS $$

BEGIN

   return date_trunc('month',$1);

END;

$$LANGUAGE plpgsql

IMMUTABLE;

 

 

CREATE INDEX computedIdx ON gameuser_daily_activity (my_date_trunc_month(login_date));

 

select count(DISTINCT gameuser_fk) from gameuser_daily_activity where my_date_trunc_month(login_date)=my_date_trunc_month('2012-01-12'::date)

       

select count(DISTINCT userid)

from users

where

login_date >= '2012-01-12'::date and

login_date < '2012-01-12'::date + '1month'::interval