Re: Query performance issue

[David Rowley]

On Sat, 5 Sep 2020 at 10:20, Nagaraj Raj  wrote:
> 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

In addition to that index, you could consider moving away from
standard SQL and use DISTINCT ON, which is specific to PostgreSQL and
should give you the same result.

EXPLAIN ANALYZE
SELECT DISTINCT ON (serial_no) serial_no,receivingplant,sku,r3_eventtime
FROM receiving_item_delivered_received
WHERE eventtype='LineItemdetailsReceived'
  AND replenishmenttype = 'DC2SWARRANTY'
  AND coalesce(serial_no,'') <> ''
ORDER BY serial_no,eventtime DESC;

The more duplicate serial_nos you have the better this one should
perform.  It appears you don't have too many so I don't think this
will be significantly faster, but it should be a bit quicker.

David

Re: Query performance issue

[Michael Lewis]

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 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 = 64
> RAM = 512
> show shared_buffers = 355 GB
> show work_mem = 214 MB
> show maintenance_work_mem = 8363MB
> show 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.

Re: Query performance issue

[Nagaraj Raj]

 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 
 wrote:  
 
 

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 
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.