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 1kEg4N-0006Go-O0 for pgsql-performance@arkaria.postgresql.org; Sat, 05 Sep 2020 21:50:00 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kEg4L-00066u-9Z for pgsql-performance@arkaria.postgresql.org; Sat, 05 Sep 2020 21:49:57 +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 1kEg4K-00066S-Cb for pgsql-performance@lists.postgresql.org; Sat, 05 Sep 2020 21:49:57 +0000 Received: from sonic310-24.consmr.mail.ne1.yahoo.com ([66.163.186.205]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kEg4G-0003pV-O8 for pgsql-performance@lists.postgresql.org; Sat, 05 Sep 2020 21:49:55 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1599342589; bh=I72X3KI8nIr7bU7hlyHKGLWfRm0n9Ucxpa1Mol9433w=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=ludflOE9pqKKWUOdVjwfnAHghMFWn7p37hVn8p6RJs3nUdhkPy3DFZwjdN/c6JcYriTz5CJsd9TXao/5lBJOdCRMLgohmzcZq5Da8HHysuQtlQxzuiM/vAan1aIX9VwXsm+D1J4T8H4i/bY0b2Rmr3KhZ5yXHF1ruNKn+M5uWH/kkmL4ABnwMA9AvKIVGaDih7nJcA++UrEgPIel64Gp5Ij+t+sObFTFKQw5A+d0fYXGsUsp3R7ltyCQVlXj/L302ZbFOwDEZF2zGNDRX0vMRNQ8+FEi3fUKsbc/vkyoKymeZ6j7QIkI4j3yl6R/tR7c9m3Y1Hch1WLPZchvTVb6fw== X-YMail-OSG: .dNBHi8VM1lp5KBkZMmYRQ.mVOZ5kUR474L7Y87pBUYFMLZb6Ub8RDB.BFKoPy1 V6UEaPMPs0pOkg.0W7afqbILnVL9VvbJo1Bk4HL1u5SjYi6OvDo122DHPP1WD5BP6P4Y5i3js839 a2kZ.QI4JvlafTv3uBUYYoLMbs8uUpFazDWt5_ZN5byerpgNFlvFZ5dqhqaaksXIQDuvo2mGnYxM Sq5a5qHYSeBZyJ3IRSpdQ3DncImksTKcqk68JyRCsDt35TEvcsBqV2YyRQrAF66WQA21F0zDcpMh s7D6BsItZvGVJxsVtthT.C64vPfMcoWY9VosN4qyHMaAuQzLJBkE4jtnJosEUsMRiXVvcPwZzW5w T2hWE57UPrYa3o3h48DG6KkJSMPOhS3.Fk8vv_XFmXkj_9xFhYQqzILti.2CoqaAcTpt46cQkarK jWga4FM7HM.utUHiPfrI6aLiLjQI6e1RWYRhdx6WDH6ZacZrvF8iTDfeJ2Li69A5F6f3pezo5_.u paSO7wX687.uHskw.rnSeReviSgqkzX30SjiaSQTnw3X7AAklIXkON2DarLmYzJWl6eDHmtMg9z2 L_IcUtWbXhjsb.ZP5I9smJqX87qCQ_9WNSi5.shuOirnqp_Faf11SNd5Kb189cdQI_iIQTcwoank b6uunL.YmsiaOzI34kDID6DgD1bmciXCIlkqTNr1ctKGvl.W.ipibdpcE0SHq.QSB_5gezLl8yh_ IDdRwOo3f9oIiBZ8_Wxj_sSmmcsaq2kmTKLYoUhoHQoPFYc85jx7gRhw8TtOier0MjQcU2BQgaS2 Y11E6x4nyzpn0KRCF_jO79x7V_SqBd5aDS_hjn2VsCIDrhCWjDA3cXPTKkvENqLgDZWVQ9eTxQdN Ai8FIOAfUN1EXy8psyawQmt.BgtCUTnMsgDLfTD2Rv7K_arVQ4Iw7rPSCL4DzbyjZFt_IG1fnNo4 u1CoR.FEvB4BaoPA79vTECUOdndzY7aI9Ihfy3gJxC68EtinNDlzkt3FkJEdF1gE0i46BvEa5_r3 2aP.v5VY9uLf3SKzxj2VcDvmbBQ1F_8dHTV4sV8EHtJU8kO2734UpK4RLGnQkzOD0iKf.XGg07.N .YR2wP89vzjPj_cyxFEzasV2b5zWv6gcmkvOkg.39v9iMVASWeM.C8B9DpaPNaruyyQrTzPSKTOg UtiGDmovwdfIn7Bvsk9.nstrhEZ.twYM8vzyDYH3GThhFy9BmqT.ie7L1yyH_8cMJbUAefVqPXj4 qO6OFO0z.sjmJHnwflwdWktKlpCcpCvwbY66uaT.Wd9yeYcZqs2106e0kSabtoON6rxIfee7ZvZ9 ebx00JQvdeLIoLXCJ3fkc7nJiTSfSBJK9qBD1rMVqIs8n2AdhvyvUYC1.kpbLZD78BSjjLuoVd02 ie19mzQYddLdZa5PmgkMgjknVDLqsvasi4gMuIpmvZ9DbrznvbCCLiCXsG464YXQ3JdbCNjCZ401 IRRRsm6j4.aeP8Oxs Received: from sonic.gate.mail.ne1.yahoo.com by sonic310.consmr.mail.ne1.yahoo.com with HTTP; Sat, 5 Sep 2020 21:49:49 +0000 Date: Sat, 5 Sep 2020 21:49:48 +0000 (UTC) From: Nagaraj Raj To: Michael Lewis Cc: Thomas Kellerer , Pgsql Performance Message-ID: <1214821634.632788.1599342588789@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> <2010108831.405412.1599255854719@mail.yahoo.com> <1574190552.3418086.1599258006641@mail.yahoo.com> Subject: Re: Query performance issue MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_632787_1301865349.1599342588787" 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: 10708 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_632787_1301865349.1599342588787 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Hi Michael, I created an index as suggested, it improved.=C2=A0 I was tried with partia= l index but the planner not using it. also, there is no difference even with=C2=A0timing OFF.=C2=A0ktbv : Optimiz= ation for: plan #HaOx | explain.depesz.com |=20 |=20 | |=20 ktbv : Optimization for: plan #HaOx | explain.depesz.com | | | Thanks,Rj On Saturday, September 5, 2020, 06:42:31 AM PDT, Michael Lewis wrote: =20 =20 =20 On Fri, Sep 4, 2020, 4:20 PM Nagaraj Raj wrote: Hi Mechel, I added the index as you suggested and the planner going through the bitmap= =C2=A0index scan,heap and the new planner is,HaOx | explain.depesz.com |=20 |=20 | |=20 HaOx | explain.depesz.com | | | Mem config:=C2=A0 Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3,= 64-bit vCPU =3D 64RAM =3D 512show shared_buffers =3D 355 GBshow work_mem =3D 214 M= B show maintenance_work_mem =3D 8363MBshow effective_cache_size =3D 355 GB I'm not very familiar with Aurora, but I would certainly try the explain an= alyze with timing OFF and verify that the total time is similar. If the sys= tem clock is slow to read, execution plans can be significantly slower just= because of the cost to measure each step. That sort being so slow is perplexing. Did you do the two column or four co= lumn index I suggested? Obviously it depends on your use case and how much you want to tune this sp= ecific query, but you could always try a partial index matching the where c= ondition and just index the other two columns to avoid the sort. =20 ------=_Part_632787_1301865349.1599342588787 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi Michael,


I created an index as su= ggested, it improved.  I was tried with partial index but the planner = not using it.

also, there is no difference even with&= nbsp;timing OFF. 

=20
On Saturday, September 5, 2020, 06:42:31 AM PDT, Michae= l Lewis <mlewis@entrata.com> wrote:




On Fri, Sep 4, 2020, 4:20 PM Nagaraj Raj = <nagaraj.sf@yahoo= .com> wrote:
Hi Mechel,

I added the index as you suggested and the planner= going through the bitmap index scan,heap and the new planner is,
<= br clear=3D"none">

HaOx | explain.depesz.com

=


Mem co= nfig: 

Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu,= compiled by gcc (GCC) 4.9.3, 64-bit
vC= PU =3D 64
RAM =3D 512
show shared_buffers =3D 355 GB
show work_mem =3D 214 MB
show maintenance_work_mem =3D 8363MB
show effective_c= ache_size =3D 355 GB
=

I'm not very familiar with Aurora,= but I would certainly try the explain analyze with timing OFF and verify t= hat the total time is similar. If the system clock is slow to read, executi= on plans can be significantly slower just because of the cost to measure ea= ch step.

That sort being so slow is= perplexing. Did you do the two column or four column index I suggested?

Obviously it depends on your use case= and how much you want to tune this specific query, but you could always tr= y a partial index matching the where condition and just index the other two= columns to avoid the sort.
------=_Part_632787_1301865349.1599342588787--