Any advice tuning this query ? - Mailing list pgsql-performance

Hi,

I have a select moving around a lot of data and takes times
Any advice tuning this query ?

EXPLAIN (ANALYZE ON, BUFFERS ON)
    select
    d.books,
    d.date publish_date,
    extract(dow from d.date) publish_dow,
    week_num_fixed,
    coalesce(sum(case when i.invno is not null then 1 else 0 end),0) as daily_cnt,
    coalesce(sum(i.activation_amount_sek),0) as daily_amt_sek
    from dates_per_books d
    left join publishing_data i on (d.books=i.books and d.date=i.publish_date)
    group by 1,2,3,4;

( explain : https://explain.depesz.com/s/aDOi )
    
                                                                                            QUERY PLAN                                                                                        
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost=44606264.52..48172260.66 rows=4318263 width=68) (actual time=839980.887..1029679.771 rows=43182733 loops=1)
   Group Key: d.books, d.date, (date_part('dow'::text, (d.date)::timestamp without time zone)), d.week_num_fixed
   Buffers: shared hit=3, local hit=10153260 read=165591641, temp read=2097960 written=2097960
   I/O Timings: read=399828.103
   ->  Sort  (cost=44606264.52..45104896.89 rows=199452945 width=48) (actual time=839980.840..933883.311 rows=283894005 loops=1)
         Sort Key: d.books, d.date, (date_part('dow'::text, (d.date)::timestamp without time zone)), d.week_num_fixed
         Sort Method: external merge  Disk: 16782928kB
         Buffers: shared hit=3, local hit=10153260 read=165591641, temp read=2097960 written=2097960
         I/O Timings: read=399828.103
         ->  Merge Left Join  (cost=191.15..13428896.40 rows=199452945 width=48) (actual time=0.031..734937.112 rows=283894005 loops=1)
               Merge Cond: ((d.books = i.books) AND (d.date = i.publish_date))
               Buffers: local hit=10153260 read=165591641
               I/O Timings: read=399828.103
               ->  Index Scan using books_date on dates_per_books d  (cost=0.56..1177329.91 rows=43182628 width=20) (actual time=0.005..33789.216 rows=43182733 loops=1)
                     Buffers: local hit=10 read=475818
                     I/O Timings: read=27761.376
               ->  Index Scan using activations_books_date on publishing_data i  (cost=0.57..7797117.25 rows=249348384 width=32) (actual time=0.004..579806.706 rows=249348443 loops=1)
                     Buffers: local hit=10153250 read=165115823
                     I/O Timings: read=372066.727
 Planning time: 2.864 ms
 Execution time: 1034284.193 ms
(21 rows)

(END)