I just got this crazy, stupid or maybe genius idea :-)
One thing that I've learned in this thread is that fat indexes (i.e.
some index on some_table(a,b,c,d,e,f)) is to be avoided as much as
possible.
One of our biggest indexes looks like this:
acc_trans(customer_id, trans_type, created)
For the very most of the time an index like this would do:
acc_trans(customer_id, trans_type, created)
But then there are those few troublesome customers that have tens of
thousands of transactions, they interactively inspect transaction
listings through the web, sometimes the query "give me my 20 most
recent transactions of trans_type 6" gets stuck, maybe the customer
has no transactions of trans type 6 and all the transactions needs to
be scanned through. Since this is done interactively and through our
front-end web page, we want all queries to be lightning fast.
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?