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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
