On 29/09/10 19:41, Tobias Brox wrote:
I just got this crazy, stupid or maybe genius idea :-)
Now, my idea is to drop that fat index and replace it with conditional
indexes for a dozen of heavy users - like those:
acc_trans(trans_type, created) where customer_id=224885;
acc_trans(trans_type, created) where customer_id=643112;
acc_trans(trans_type, created) where customer_id=15;
or maybe like this:
acc_trans(customer_id, trans_type, created) where customer_id in ( ... );
Any comments?
My sysadmin is worried that it would be a too big hit on performance
when doing inserts. It may also cause more overhead when planning the
queries. Is that significant? Is this idea genius or stupid or just
somewhere in between?
Yeah, I think the idea of trying to have a few smaller indexes for the
'hot' customers is a good idea. However I am wondering if just using
single column indexes and seeing if the bitmap scan/merge of smaller
indexes is actually more efficient is worth testing - i.e:
acc_trans(trans_type);
acc_trans(created);
acc_trans(customer_id);
It may mean that you have to to scrutinize your effective_cache_size and
work_mem parameters, but could possibly be simpler and more flexible.
regards
Mark
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
