Matthew Hartman wrote:
> To determine the available slots, the algorithm finds the earliest slot
> that has an available chair and a count of the required concurrent
> intervals afterwards. So a 60 minute regimen requires 12 concurrent
> rows. This is accomplished by joining the table on itself. A second
> query is ran for the same range, but with respect to the nurse time and
> an available nurse. Finally, those two are joined against each other.
> Effectively, it is:
>
> Select *
> From (
> Select *
> From matrix m1, matrix m2
> Where m1.xxxxx = m2.xxxxx
> ) chair,
> (
> Select *
> From matrix m1, matrix m2
> Where m1.xxxxx = m2.xxxxx
> ) nurse
> Where chair.id = nurse.id
>
> With matrix having 3,280 rows. Ugh.
>
> I have tried various indexes and clustering approachs with little
> success. Any ideas?
I don't understand your data model well enough to understand
the query, so I can only give you general hints (which you probably
already know):
- Frequently the biggest performance gains can be reached by
a (painful) redesign. Can ou change the table structure in a way
that makes this query less expensive?
- You have an index on matrix.xxxxx, right?
- Can you reduce the row count of the two subqueries by adding
additional conditions that weed out rows that can be excluded
right away?
- Maybe you can gain a little by changing the "select *" to
"select id" in both subqueries and adding an additional join
with matrix that adds the relevant columns in the end.
I don't know the executor, so I don't know if that will help,
but it would be a simple thing to test in an experiment.
Yours,
Laurenz Albe
