[gold-users] number of deleted entries in g_reservation

[Brock Palen]

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