Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kEJVQ-0004Oz-Vs for pgsql-performance@arkaria.postgresql.org; Fri, 04 Sep 2020 21:44:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kEJVP-0002OJ-Ui for pgsql-performance@arkaria.postgresql.org; Fri, 04 Sep 2020 21:44:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kEJVP-0002OC-Mi for pgsql-performance@lists.postgresql.org; Fri, 04 Sep 2020 21:44:23 +0000 Received: from sonic308-9.consmr.mail.ne1.yahoo.com ([66.163.187.32]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kEJVN-0008Fn-5O for pgsql-performance@lists.postgresql.org; Fri, 04 Sep 2020 21:44:23 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1599255857; bh=4VmBr6yvzY3s7MT0gZGmO/Zw2i1KIiJvW7QcQMcFyQI=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=OWdUgtXFHTLsA4QyfssCLIUgtDfQSVn+SBV8ERNCY3UwwSDIG3fvXcGWUgVxZ3pX3u4I59FSEJfxXSM0VqA1j0oYHy0Y6lkRFWjiCMNerIUNax3T++wezv7uNM1hlq6jHH7DwAmcGttHOpHHf++j4tYwNEGet2pUpQzzSKgBEB9SCIZUyT1k44wwJvOZ5pjeKH5pinZCp1RP8Uy79mfXRhIV1IaoP+uZ2UUKTzVOFbFnCrcIoTgSKxuCvsOfN72F9Dl1bAWu5d6FfYa09a5VxgnjHn8xjwOGI76HqS2wclkg1wShjpsV882ZZKr2eCGNQsoaBR+EteC+61y+woOinw== X-YMail-OSG: hvHuP0cVM1kopRfPux7fL1z0k2WWxRWib1aYyuzLGLxqwod8AmoVpFBssERt65k DQVF9hdZuBIojPZhzGZWBKBBWz5YVah0XDqF1onFoLrl.I_zQ9zFXRw_HSLgBHcGHvLquVhNffmd G87zgekNoc786nw_f3rouD7A27q.3nB_s3G8zbkH645zMP1Ge.xEe_4j87Laks6fAaQCPLgs_w7b QBI_WOAkukWGaZfGeA42rPOyd2MfVTMPnGQcL9X0Jns_TFvAkJ_GEpQDTzcTvZQnihGV.qGo2Ynu 2U_XebpJdmrD91aj1IZo4nQzvizDSRsB1WYC0rwxUUflx9WJiqRJKsz4YbzoFl4fVrmlk2AS4wXf UMSqXckyu2zd..UKMy1MPGwTB4crDnkQmMln9GlS3b7tQVDEhhZtMg.ulD_cnMs5BO8pAP3Y3_tj .Zg895OeeZ5NKwn_soi4L_RwOkyFgzvEQq55vt2dyNwPCzppm5aOCUJgoJqTIXBNvGWoPN3bYIqQ lg9jEcZsNAT..HntPXkwaFEMdnM0beUO4eXY9LL8.EB7imZBEAlO5ay_tO.bTQ0OzDStiQtwYAjY bwKhz.kGiORaRQW9pPlvieg1J.w57_5Rds3WRkoohu4_ue_KHw8MwgJZVWv4QlcF3r.g2dDStvPx eCmw_6h5DsOPVtr355xvwVWw9U2oCKg6dqUwotckGq0dbaYX7tQH8PoMazGJMXdeXhLc9JVBkuh8 j0r1unLs94CECZcF0DPt76YnPOkb08_f1ZF_k_RTUT4El6NVQ68eSnMRkPJNSgKxcE7YbiOxYC6r .FpA1CB7XAfxOVBiggAOFGO_GYMy0RNDHsd9iA3e2BncST6bDqYa6LKMjfbbRiFr8G6qZ6Ilrl_b 3fRDQX6XXkpZD_xamkPr1L20saQ72NcNgAquc8NRJrVa3D9R6xzNjh8WBCn9oF7ts8qFWHxKzSxG KMjLLBEcsKwLzFrqEyhJKf2SVeHSDNnXVzjaz9Yqz5aTX8vNmBVdCLYderO7g0sK.QrjwMuGUm9. EoZpoIULm0EAOjtMSs2d3IBPFrACGAAYFghws.X_0NnPNTJYW7hD7LyHgjktZsMT5AEaZ85rBD19 TzWjZHsBBDeOWkwiYz8yz98uj1pVs3tuq1B6qaRLbQWbGYqv05jR8XqxUBY6ZxhjsGb.SHFR8..v ybI23eX23BLlIZKXz3g.lic85N2hc6xTVJe1JANXHBAeSIR0CRDQ29BCuEwwXkR6.sL7oTfywVwR n1hFjO_RNE06o0xU8aKEp1gNhMlIDvIZNGXeIiBeRtE2ddjMK7YgxhEWezCBc882XPc5wOiCnIli 9LzLoJumbfjb1.DRmSiT4DSWyTuBKFvhFbNqIBFKAqY7inWsj7mcDADjMDOWX290aWZpqjtW4QgP LNtuhyx1mMktzb_vkP0Mh9tf0DxCN7mpfsCBMjuIAGhoSwc0BYj5LmYvG7vSHA.VECPj8xkURwXq 1Rzlz8QS5wkIa9vYHSrnKJy_EKcJEbtXlgw-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic308.consmr.mail.ne1.yahoo.com with HTTP; Fri, 4 Sep 2020 21:44:17 +0000 Date: Fri, 4 Sep 2020 21:44:14 +0000 (UTC) From: Nagaraj Raj To: Michael Lewis , Thomas Kellerer Cc: Pgsql Performance Message-ID: <2010108831.405412.1599255854719@mail.yahoo.com> In-Reply-To: References: <975305787.3395625.1599254321500.ref@mail.yahoo.com> <975305787.3395625.1599254321500@mail.yahoo.com> <499793818.3392649.1599254645140@mail.yahoo.com> Subject: Re: Query performance issue MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_405411_352708691.1599255854718" X-Mailer: WebService/1.1.16565 YMailNorrin Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/85.0.4183.83 Safari/537.36 Content-Length: 6037 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_405411_352708691.1599255854718 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Sorry, I have attached the wrong query planner, which executed in lower en= vironment which has fewer resources: Updated one,eVFiF | explain.depesz.com |=20 |=20 | |=20 eVFiF | explain.depesz.com | | | Thanks,Rj On Friday, September 4, 2020, 02:39:57 PM PDT, Michael Lewis <= mlewis@entrata.com> wrote: =20 =20 CREATE INDEX receiving_item_delivered_received ON=C2=A0receiving_item_deli= vered_received USING btree ( eventtype, replenishmenttype, serial_no, event= time DESC ); =20 More work_mem as Tomas suggests, but also, the above index should find the = candidate rows by the first two keys, and then be able to skip the sort by = reading just that portion of the index that matches=20 eventtype=3D'LineItemdetailsReceived'and replenishmenttype =3D 'DC2SWARRANT= Y' =20 ------=_Part_405411_352708691.1599255854718 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Sorry, I have attached the w= rong query planner, which executed in lower environment which has fewer res= ources:

Updated one,



Thanks,
Rj
=20
=20
On Friday, September 4, 2020, 02:39:57 PM PDT, Michael = Lewis <mlewis@entrata.com> wrote:


CREATE= INDEX receiving_item_delivered_received ON receiving_item_delivered_r= eceived USING btree ( eventtype, replenishmenttype, serial_no, eventtime DE= SC );

M= ore work_mem as Tomas suggests, but also, the above index should find the c= andidate rows by the first two keys, and then be able to skip the sort by r= eading just that portion of the index that matches


eventtype=3D'LineItemdetailsReceived'
and replenishmen= ttype =3D 'DC2SWARRANTY'
------=_Part_405411_352708691.1599255854718--