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)