|
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)
|
