Re: Speeding up a query.

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