From: Nagaraj Raj <nagaraj.sf@yahoo.com>
To: Michael Lewis <mlewis@entrata.com>
Cc: Thomas Kellerer <shammat@gmx.net>
Cc: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: Re: Query performance issue
Date: Sat, 5 Sep 2020 21:49:48 +0000 (UTC)
Message-ID: <1214821634.632788.1599342588789@mail.yahoo.com> (raw)
In-Reply-To: <CAHOFxGq9PS--cKB4rG_oLRSUO+FoisidQs29GpstQDQ8pg_bgw@mail.gmail.com>
References: <975305787.3395625.1599254321500.ref@mail.yahoo.com>
<975305787.3395625.1599254321500@mail.yahoo.com>
<499793818.3392649.1599254645140@mail.yahoo.com>
<CAHOFxGo-YY-0RMzEx=JzNiu_M6fqpd1TskWDLGZ8xvfFEu813A@mail.gmail.com>
<2010108831.405412.1599255854719@mail.yahoo.com>
<CAHOFxGr_Fg1sODNJwjub90MFXBASBviM6XfRjTyi-+yab0p6xw@mail.gmail.com>
<1574190552.3418086.1599258006641@mail.yahoo.com>
<CAHOFxGq9PS--cKB4rG_oLRSUO+FoisidQs29GpstQDQ8pg_bgw@mail.gmail.com>
Hi Michael,
I created an index as suggested, it improved. I was tried with partial index but the planner not using it.
also, there is no difference even with timing OFF. ktbv : Optimization for: plan #HaOx | explain.depesz.com
|
|
| |
ktbv : Optimization for: plan #HaOx | explain.depesz.com
|
|
|
Thanks,Rj
On Saturday, September 5, 2020, 06:42:31 AM PDT, Michael 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,HaOx | explain.depesz.com
|
|
| |
HaOx | explain.depesz.com
|
|
|
Mem config:
Aurora PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit
vCPU = 64RAM = 512show shared_buffers = 355 GBshow work_mem = 214 MB
show maintenance_work_mem = 8363MBshow effective_cache_size = 355 GB
I'm not very familiar with Aurora, but I would certainly try the explain analyze with timing OFF and verify that the total time is similar. If the system 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 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 try a partial index matching the where condition and just index the other two columns to avoid the sort.
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: nagaraj.sf@yahoo.com, mlewis@entrata.com, shammat@gmx.net, pgsql-performance@lists.postgresql.org
Subject: Re: Query performance issue
In-Reply-To: <1214821634.632788.1599342588789@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox