On 9/29/06, Carlo Stonebanks <stonec.register@xxxxxxxxxxxx> wrote:
For reasons I've exlained elsewhere, the import process is not well suited to breaking up the data into smaller segments. However, I'm interested in what can be indexed. I am used to the idea that indexing only applies to expressions that allows the data to be sorted, and then binary searches can be performed on the sorted list. For example, I can see how you can create an index to support: where foo like 'bar%' But is there any way to create an index expression that will help with: where foo like '%bar%'? I don't see it - but then again, I'm ready to be surprised!
using standard (btree) index, you can create an index on any constant expression. so, you can create in index that matches '%bar%, but if you also want to match '%bat%', you need another index. there are other exotic methods like t_search and gist approach which may or may not be suitable. regarding your import process, you came to this list and asked for advice on how to fix your particular problem. tweaking postgresql.conf, etc will get you incremental gains but are unlikely to have a huge impact. as i understand it, your best shot at improvement using current process is to: 1. fork your import somhow to get all 4 cores running 2. write the code that actually does the insert in C and use the parameterized prepared statement. however, your general approach has been 'please give me advice, but only the advice that i want'. if you really want to fix your problem, give more specific details about your import and open the door to improvements in your methodology which i suspect is not optimal. you concluded that client side coding was the way to go, but here you are asking how to make it work. if you want help (and there are some extremely smart people here who may give you world class advice for free), you need to lay your cards on the table and be willing to consider alternative solutions. you may find that a few properly consructed queries will knock out 75% of your code and running time. remember often the key to optimization is choosing the right algorithm merlin
