There is a table with 434 thousand lines. Fields - timestamp, xuid and titleid. I make annual, quarterly and monthly statistics on them:

xbox=> explain (analyze,buffers) select extract(year from date) as year, extract(quarter from date) as quarter,extract(month from date) as month, count(distinct xuid) as xuids,count(distinct titleid) as titles from sessions group by 1,2,3 order by 1 desc,2 desc,3 desc; QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=144942.51..151455.73 rows=96 width=24) (actual time=123228.641..131120.022 rows=13 loops=1) Group Key: (date_part('year'::text, date)), (date_part('quarter'::text, date)), (date_part('month'::text, date)) Buffers: shared hit=10276 read=86421 -> Sort (cost=144942.51..146027.77 rows=434103 width=24) (actual time=123039.396..123075.458 rows=434076 loops=1) Sort Key: (date_part('year'::text, date)), (date_part('quarter'::text, date)), (date_part('month'::text, date)) Sort Method: quicksort Memory: 46201kB Buffers: shared hit=10276 read=86421 -> Seq Scan on sessions (cost=0.00..104293.80 rows=434103 width=24) (actual time=0.086..1063.351 rows=434076 loops=1) Buffers: shared hit=10276 read=86421 Planning time: 0.113 ms Execution time: 131125.541 ms (11 rows) xbox=> select extract(year from date) as year,extract(quarter from date) as quarter, extract(month from date) as month,count(distinct xuid) as xuids, count(distinct titleid) as titles from sessions group by 1,2,3 order by 1 desc,2 desc,3 desc; year | quarter | month | xuids | titles ------+---------+-------+-------+-------- 2016 | 4 | 11 | 378 | 353 2016 | 4 | 10 | 499 | 651 2016 | 3 | 9 | 493 | 678 2016 | 3 | 8 | 492 | 708 2016 | 3 | 7 | 486 | 719 2016 | 2 | 6 | 499 | 670 2016 | 2 | 5 | 493 | 695 2016 | 2 | 4 | 499 | 676 2016 | 1 | 3 | 486 | 628 2016 | 1 | 2 | 410 | 601 2016 | 1 | 1 | 397 | 606 2015 | 4 | 12 | 359 | 559 2015 | 4 | 11 | 293 | 462 (13 rows)

Why does sorting go according to 430 thousand lines before grouping? How to affect it?

Indices, besides date, are built by

 create index sessions_idx_year on sessions(extract(year from date)); create index sessions_idx_quarter on sessions(extract(quarter from date)); create index sessions_idx_month on sessions(extract(month from date));

Perhaps it is better to rewrite the query so that instead of count there is an nested select? How to do it?

    1 answer 1

    And if you try this way (there is no place to verify the syntax, alas):

     select extract(year from s.date) as year, extract(quarter from s.date) as quarter, extract(month from s.date) as month, s.xuids as xuids, s.titles as titles FROM ( select date, count(distinct xuid) as xuids, count(distinct titleid) as titles from sessions group by 1,2,3 ) AS s order by 1 desc,2 desc,3 desc;

    Assumption: first, recalculations, then only work with dates, and only then sorting.