Building a large PHP + PostgreSQL hierarchy table, fastest output per page?

Question

PHP

Good afternoon.
There is a task of the following type - there is a PostgreSQL database:
- the staff table contains a parent-child hierarchy, now there are about 500 records;
- the sales table contains information about sales for each day, but sales are recorded only at the lower level of the hierarchy;
- the plans table contains the plans of everyone from the staff table by months;
With one query to the database, I pull out the entire hierarchy with join, add plans and sales to it, summing up on the parent. The request itself is executed quickly, phpPgAdmin issues 330.565 ms when executing the request, of course I would like it to be faster, but faster, it seems to me, is no longer possible.
At the output, I get a two-dimensional array, with which I now have to work. Now everything works through a recursive enumeration of the array, because You also need to maintain a hierarchy.
The logic is as follows - at the input I have the id of the very first parent, in my case id=1. The function works like this, it finds all the descendants with parent_id=1 through the self-written arraySearch() function and writes them to a new array, in my case there are 6 of them, then the function is called again and finds the descendants of these 6 and etc. Each time it finds a value, arraySearch() removes the found values ​​from the original array, thereby reducing the array to speed up the search with each subsequent iteration. The function continues output until the array is empty. Everything works, but it's slow. I'm sure there's a faster way, but I'm running out of ideas.
If you have any ideas, please suggest. Also, please advise on request, perhaps you can complete the request faster.
Thanks in advance.

Answer 1

There was recently a question about the hierarchy - How to display a category tree from the database (Java / jsp)?

Answer 2

NESTED SET