On Thu, Oct 22, 2020 at 12:32:29AM +0000, Nagaraj Raj wrote: > Hi, I have long running query which running for long time and its planner > always performing sequnce scan the table2.My gole is to reduce Read IO on the > disk cause, this query runns more oftenly ( using this in funtion for ETL). > > table1: transfer_order_header(records 2782678)table2: transfer_order_item ( > records: 15995697)here is the query: > > set work_mem = '688552kB';explain (analyze,buffers)select > COALESCE(itm.serialnumber,'') AS SERIAL_NO, > COALESCE(itm.ITEM_SKU,'') AS SKU, COALESCE(itm.receivingplant,'') > AS RECEIVINGPLANT, COALESCE(itm.STO_ID,'') AS STO, supplyingplant, > COALESCE(itm.deliveryitem,'') AS DELIVERYITEM, min(eventtime) as > eventtime FROM sor_t.transfer_order_header hed,sor_t.transfer_order_item itm > where hed.eventid=itm.eventid group by 1,2,3,4,5,6
It spends most its time writing tempfiles for sorting, so it (still) seems to be starved for work_mem. |Sort (cost=1929380.04..1946051.01 rows=6668390 width=172) (actual time=50031.446..52823.352 rows=5332010 loops=3) First, can you get a better plan with 2GB work_mem or with enable_sort=off ? If so, maybe you could make it less expensive by moving all the coalesce() into a subquery, like | SELECT COALESCE(a,''), COALESCE(b,''), .. FROM (SELECT a,b, .. GROUP BY 1,2,..)x; Or, if you have a faster disks available, use them for temp_tablespace. -- Justin
