We have:
gold=> select count(*) from g_reservation where g_deleted='True';
count
---------
4316107
Vs:
gold=> select count(*) from g_reservation where g_deleted!='True';
count
-------
666
Should we really have 4 million deleted reservations? This grew a lot
and slowed down queries like these:
SELECT g_reservation_allocation.g_id,g_reservation_allocation.g_amount
FROM g_reservation, g_reservation_allocation WHERE
( g_reservation.g_id=g_reservation_allocation.g_reservation AND
g_reservation.g_start_time<='1268150891' AND
g_reservation.g_end_time>'1268150891' AND
( g_reservation_allocation.g_id='6' OR
g_reservation_allocation.g_id='4' OR g_reservation_allocation.g_id='7'
OR g_reservation_allocation.g_id='2' OR
g_reservation_allocation.g_id='5' ) ) AND g_reservation.g_deleted!
='True' AND g_reservation_allocation.g_deleted!='True';
Note to speed this up, we created a partial index of the format:
create index g_id_not_deleted_idx ON g_reservation (g_deleted) WHERE
NOT g_deleted='True';
That lets the above query look at only 666 lines not 4 million, query
times went from 150ms to 10ms.
Brock Palen
www.umich.edu/~brockp
Center for Advanced Computing
brockp at umich.edu
(734)936-1985