Re: Unable to execute Query in parallel for partitioned table

Hi Albe,

We have checked as per your suggestion and we are good now.

Thank you !!!

On Thu, 11 Feb, 2021, 8:49 PM Brajendra Pratap, <
brajendra(dot)pratap767(at)gmail(dot)com> wrote:

> Hi Albe,
>
> Thank you so much for information, will check this and get back to you if
> any help required.
>
> I have a doubt why didn't the parallelism works here ,could u plz guide me?
>
> Thank you so much again.
>
> On Thu, 11 Feb, 2021, 1:23 PM Laurenz Albe, <laurenz(dot)albe(at)cybertec(dot)at>
> wrote:
>
>> On Thu, 2021-02-11 at 05:09 +0530, Brajendra Pratap wrote:
>> > 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.
>>
>> I don't think parallelization will help you here.
>>
>> Your problem is probably the "abc.*" in the SELECT list.
>>
>> There must be really large data in this table, so it takes a long time to
>> fetch and
>> sort the rows. Try selecting only the columns you need.
>>
>> Alternatively, add a LIMIT clause. Do you really need all 800000 rows?
>>
>> Yours,
>> Laurenz Albe
>> --
>> Cybertec | https://www.cybertec-postgresql.com
>>
>>