Richard Warburton wrote: > For example, this takes minutes: CPU Time: user 263.759513 sys 2.237379 > SELECT s.Id, s.BookingId, s.At, s.DateIn, s.DateOut, s.RoomUID, > s.RatePlanUID FROM StayV s > WHERE NOT EXISTS (SELECT NULL FROM InvoiceItemV i WHERE i.PriceUID IS NULL > AND i.StayUID=s.UID); > > While this takes tenths of seconds for the same result: CPU Time: user > 0.231575 sys 0.021046 > SELECT s.Id, s.BookingId, s.At, s.DateIn, s.DateOut, s.RoomUID, > s.RatePlanUID FROM StayV s > WHERE NOT EXISTS (SELECT NULL FROM InvoiceItem i WHERE i.PriceUID IS NULL > AND i.StayUID=s.UID AND EXISTS (SELECT NULL FROM InvoiceItemV v WHERE > i.UID=v.UID)); > > The latter looks like more work yet is 1000 times faster. Thoughts?
In the EXPLAIN QUERY PLAN output, the second query has an additional lookup in the InvoiceItem table, using an index. It's possible that this reduces the number of unindexed lookups in the same table. Anyway, experimenting shows that indexes on InvoiceItem(StayUID, PriceUID) and InvoiceItem(Id, At) would be helpful. Regards, Clemens
