ORM Search advanced filters inefficient

[Kyle Waid]

ORM generates inefficient SQL queries for use in advanced filters. Use stock.picking as example. I have filter buttons at the top.
Ready, Waiting, Backorder, etc. Additionally I have search view fields like origin, partner, reference. When I use these filters and fields it narrows down the results for me.

Now lets consider advanced filters. When I select an advanced filter and search, the ORM queries the database for all records matching the textual search with ilike. It does a query with only the advanced filter. It does not consider the filters I previously selected. Here is a full example

I have 1 million stock.picking records.
I filter the records based on available and waiting status. This narrows down the results from 1 million to 100.
I then want to filter those records even further by creating an advanced filter. Instead of filtering the remaining results, it does a textual search based on the entire table, not what I already have filtered. After it fetches these results, it again queries the database to filter based on my results. This is extremely inefficient.

IMO this feature needs to be refactored.
Some more context. I am trying to create a feature that allows a user to search pickings that contain specific products. In this database there are 3.5 million stock moves. By using an advanced filter it hits the database by searching the entire table every time regardless of any filters already in place. It could take over 1 minute to return a result, but if it used the previously filtered conditions it would only take a few seconds.

Also consider the user has already filtered results, so what good does it do to search records that will never be returned.