Postgresql database getting slow (selection) for millions records

Question

Postgresql database getting slow when trying to fetch data for millions records. I tried with Materialized view, however performance was drastically fast but it doesn't give real time data.

I am also using aggregation ex. sum, count, group by clause etc...

SELECT offer_id as off_id,
  COUNT(distinct ip) as hosts,
  COUNT(distinct click_id) as clicks 
FROM offer_affiliate_stats 
WHERE 
  created_dt >= '2019-06-01' 
AND 
  created_dt  <= '2019-06-30' 
GROUP BY off_id;

I have tried with Materialized view.

Indexes are applied on id, created_dt, click_id

My output should be like this:

off_id               | 79
hosts                | 4
clicks               | 4
offer_name           | "Testing Javelin"
offer_id             | 
total_conversions    | 
total_income         | 
optimised_count      | 
optimised_income     | 
approved_income      | 
approved_conversions | 
declined_income      | 
declined_conversions | 
total_payout         | 

Actually without using distinct keyword it work flawlessly but when I use distinct it takes long time.

Answer 1

Should you configure your database?

You see below link: https://www.postgresql.org/docs/current/runtime-config-resource.html

Especially work_mem default value is 4MB. You may increase to 100MB.

You change code like:

SELECT  offer_id as off_id,
        COUNT(ip) as hosts,
        COUNT(click_id) as clicks 

FROM 
(select distinct offer_id,
        ip ,
        click_id    
        from offer_affiliate_stats 
WHERE created_dt >= '2019-06-01' 
AND created_dt  <= '2019-06-30' ) as t
GROUP BY off_id;