[gold-users] number of deleted entries in g_reservation

Brock Palen brockp at umich.edu
Tue Mar 9 10:46:26 MST 2010

We have:

gold=> select count(*) from g_reservation where g_deleted='True';


gold=> select count(*) from g_reservation where g_deleted!='True';

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
Center for Advanced Computing
brockp at umich.edu

More information about the gold-users mailing list