On Tue, 2009-06-16 at 14:35 -0400, Hartman, Matthew wrote: > The algorithm for packing appointments in respects each constraint and > typically schedules a day of treatments (30-60) within 9-10 seconds on > my workstation, down from 27 seconds initially. I would like to get it > below 5 seconds if possible. > > I think what's slowing is down is simply the number of rows and joins. > The algorithm creates a scheduling matrix with one row per 5 minute > timeslot, per unit, per nurse assigned to the unit. That translates to > 3,280 rows for the days I have designed in development (each day can > change). ISTM the efficiency of your algorithm is geometrically related to the number of time slots into which appointments might fit. So reduce number of possible time slots... Assign the slot (randomly/hash/round robin) to either the morning or the afternoon and then run exactly same queries just with half number of time slots. That should reduce your execution time by one quarter without using multiple CPUs for each morning/afternoon. Then run twice, once for morning, once for afternoon. You could parallelise this and run both at same time on different CPUs, if the extra work is worthwhile, but it seems not, judging from your requirements. Another way would be to arrange all appointments that need odd number of timeslots into pairs so that you have at most one appointment that needs an odd number of timeslots. Then schedule appointments on 10 minute boundaries, rounding up their timeslot requirement. (The single odd timeslot appointment will always waste 1 timeslot). Hope that helps. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
