Hi,
I am unable to execute the below in parallel plz suggest how can I achieve parallelism here.
select count(*) over () VIEWALLROWCOUNT,abc.*,ROW_NUMBER() OVER (order by trn_transaction_date desc ) AS RowNumber from (
select * from transactions where trn_store_date_id=20201202) abc;
Query plan is as mentioned below :-
explain analyze select count(*) over () VIEWALLROWCOUNT,abc.*,ROW_NUMBER() OVER (order by trn_transaction_date desc ) AS RowNumber from (
select * from transactions where trn_store_date_id=20201218) abc;
LOG: duration: 25820.176 ms statement: explain analyze select count(*) over () VIEWALLROWCOUNT,abc.*,ROW_NUMBER() OVER (order by trn_transaction_date desc ) AS RowNumber from (
select * from transactions where trn_store_date_id=20201218) abc;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WindowAgg (cost=4474843.51..4498066.81 rows=774110 width=21297) (actual time=21455.495..25241.738 rows=795190 loops=1)
-> WindowAgg (cost=4474843.51..4488390.44 rows=774110 width=21289) (actual time=10588.494..15311.865 rows=795190 loops=1)
-> Sort (cost=4474843.51..4476778.79 rows=774110 width=21281) (actual time=10588.422..11771.300 rows=795190 loops=1)
Sort Key: transactions.trn_transaction_date DESC
Sort Method: external merge Disk: 1496856kB
-> Result (cost=0.00..270640.32 rows=774110 width=21281) (actual time=0.117..4504.159 rows=795190 loops=1)
-> Append (cost=0.00..262899.22 rows=774110 width=21281) (actual time=0.094..1449.532 rows=795190 loops=1)
-> Seq Scan on transactions (cost=0.00..0.00 rows=1 width=47554) (actual time=0.019..0.019 rows=0 loops=1)
Filter: (trn_store_date_id = 20201218)
-> Index Scan using idx_202012_trn_store_date_id on transactions_202012 (cost=0.56..259028.67 rows=774109 width=21281) (actual time=0.074..1357.764 rows=795190 loops=1)
Index Cond: (trn_store_date_id = 20201218)
Planning Time: 116.472 ms
Execution Time: 25676.098 ms
Note :- We had tried different options like max_worker_processes, max_parallel_workers, max_parallel_workers_per_gather,max_parallel_maintenance_worker to execute it in parallel but no luck.
Please suggest.
Thanks
