Postgres - Help with performance issues using without using distinct / subquery

Hi,

Were having a performance issue with postgres (11) and we need someone with expert skills to solve it!

***EDIT: BUDGET UPGRADED to $300 USD ***
***EDIT: BUDGET UPGRADED to $275 USD ***
***EDIT: BUDGET UPGRADED to $250 USD ***
***EDIT: Slow and Fast queries updated***

Problem:
Products table with about 60,000 rows and a Product Access table with about 33,000,000 rows. Neither of these tables as youll know are big by postgres standards. We have various indexes on the table already to alleviate the performance issues but nothing seems to help.

We need distinct rows for each product and all of the pagination needs to happen in postgres. There are a few order by clauses and where conditions that vary, depending on what the client is looking for.

Adding indexes for each of the order by statements is good, it allows for very quick results, since they can simply be walked. The problems arise when we try and get distinct values.

We have tried what everyone says online to do which is use a Distinct + Subquery. While the results are correct the performance is incredibly poor as all the results for the subquery are calculated (not returned) before performing the outer order by.

Since performance is really good when the index matches the order by exactly (about 1-2ms) we thought there could be some window function to remove duplicates, we just dont know how.

I will share the database schema and the only important tables. A full dump can be provided. We have also tried placing product right on products_display product access to avoid the join on a join, didnt make any appreciable difference.

$300 USD will be paid out instantly (to the first person) if a fix is included in your proposal, otherwise well make the hire and test that the solution works. For the query below the execution time should not exceed 100ms on any CPU within the last 5 years.

The slow query will give you the desired results. The first query is *close* to working, but needs to use CTE / Window Function or something to give the appropriate results fast enough. Any questions will be answered within 15 minutes.
About the recuiter
Member since Nov 11, 2022
Zhuning Zhou
from Maharashtra, India

---

---

Offer to work on this project closes in 196 days!
Are you interested in this Opportunity?

Looking for help? Checkout our video tutorial
How to search and apply for jobs

How to apply? Do you have more questions about the Job?
See frequently asked questions