Date: Wed, 21 Oct 2020 20:09:07 -0500
From: Justin Pryzby <pryzby@telsasoft.com>
To: Nagaraj Raj <nagaraj.sf@yahoo.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Query performance
Message-ID: <20201022010907.GQ9241@telsasoft.com>
References: <2026306342.2139401.1603326749019.ref@mail.yahoo.com>
 <2026306342.2139401.1603326749019@mail.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <2026306342.2139401.1603326749019@mail.yahoo.com>
User-Agent: Mutt/1.9.4 (2018-02-28)
Precedence: bulk

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