agora inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Justin Pryzby <pryzby@telsasoft.com>
To: Nagaraj Raj <nagaraj.sf@yahoo.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Query performance
Date: Wed, 21 Oct 2020 20:09:07 -0500
Message-ID: <20201022010907.GQ9241@telsasoft.com> (raw)
In-Reply-To: <2026306342.2139401.1603326749019@mail.yahoo.com>
References: <2026306342.2139401.1603326749019.ref@mail.yahoo.com>
<2026306342.2139401.1603326749019@mail.yahoo.com>
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
view thread (48+ messages) latest in thread
Message-ID: <20201022010907.GQ9241@telsasoft.com>
Permalink: ../20201022010907.GQ9241@telsasoft.com/
Also on: postgresql.org/message-id/20201022010907.GQ9241@telsasoft.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, nagaraj.sf@yahoo.com, pgsql-performance@lists.postgresql.org
Subject: Re: Query performance
In-Reply-To: <20201022010907.GQ9241@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox