[gold-users] number of deleted entries in g_reservation

Scott Jackson scottmo at adaptivecomputing.com
Tue Mar 9 17:26:10 MST 2010


Brock,

Thanks for the suggestion. I have added the following to bank.sql.in 
which will be in the next release:

@SQL_INDEX_COMMENT at create INDEX g_id_not_deleted_idx ON g_reservation 
(g_deleted) WHERE NOT g_deleted='True';

Scott


Brock Palen wrote:
> 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
>
>
>
> _______________________________________________
> gold-users mailing list
> gold-users at supercluster.org
> http://www.supercluster.org/mailman/listinfo/gold-users
>   



More information about the gold-users mailing list