Unable to execute Query in parallel for partitioned table

From: Brajendra Pratap <brajendra(dot)pratap767(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unable to execute Query in parallel for partitioned table
Date: 2021-02-10 23:39:56
Message-ID: CABdERiNs9odgugV=CwOKMq_BVKL+fWdJXDGEknA4S35+0gUC7g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Guy Burgess 2021-02-11 00:21:12 PostgreSQL occasionally unable to rename WAL files (NTFS)
Previous Message Daniel Westermann (DWE) 2021-02-10 20:38:19 Compiler warnings on Debian 10