Very poor performance on window Generate Invoice (manual) with RMA

Thanks for your info .

Hi

Thanks for your information, I got curious why that worked without the index and did some tests.

Firstly filled the table M_RMALine with 200.000 records and checked the execution of both queries:

The old query shows an execution plan with this cost:
Sort (cost=20109.92..20109.93 rows=1 width=546)

The new query shows this cost:
Sort (cost=94.74..94.74 rows=1 width=546)

I tried created an index on M_RMALine.M_RMA_ID but postgres didn't make any use of this new index, so I was wrongly assuming that would help.

Just to be on the safe side I conducted the same test in oracle, and oracle makes the same execution plan for both cases - and answer a lot faster than postgresql.

Regards,

Carlos Ruiz

Thank you Carlos!
I hope you and your family are safe and healthy!

I'm currently running on PG 12.5, but the SQL statement change in InvoiceGen.java was made when still running on PG 10.x with similar performance gains.

I just simulated a full table scan with both query versions, comparing the performance of IN vs. EXISTS using 12.5/PGAdmin and the difference is huge. Here is the query:

IN Statement:

Successfully run. Total query runtime: 12 min 58 secs. 16 rows affected.

EXISTS Statement:

Successfully run. Total query runtime: 1 secs 166 msec. 16 rows affected.

I agree, we should defintiley index foreign keys in line-tables to speed up the performance of parent-child relationships.

To make sure. I've checked all indexes currently in my database for all the tables accessed in the above query using:

There are a few custom indexes we created, but it seems none of them affecting above query or the exists-statement.
Index m_inoutline_rmaline_id I only created a few days ago to improve performance for another report and also doesn't affect above query.

Thanks also for your advice regarding the feature branch, that's very helpful.

Cheers,
Michael

Hi - thanks for the pull request 809

in the pull request wrote:

please provide the PostgreSQL version you have tested this and the corresponding query analysis that shows the new version of the SQL is more efficient.

I also took a look, first generic thing: is better to do the commit in a branch feature - as you did it in master it will become complex for you to manage keeping the pull request up to date when the master start changing

Now, about the change, it is changing this statement:

with this:

now - analyzing the new EXISTS clause it is basically the same as the IN clause - but I think it’s not really relevant to improve the performance of the query. What is really needed there is an index on M_RMALine at least for the column M_RMA_ID. I think probably you created this index on your database, and the patch would be incomplete and probably EXISTS performance can be worst without such index.

So, I think instead of this proposed change, the only thing needed is probably the index, and by the way, there must be a lot of “line” tables where an index to resolve the foreign key on the parent document is worthy, maybe we can do something generic to add those indexes to the most common tables: C_OrderLine, C_InvoiceLine, M_*Line, etc

Can you please check your DB, I think you must have an index created to make the change work.

Regards,

Carlos Ruiz

Thanks Carlos, I submitted a pull request to idempiere/master.