Re: Performace Optimization for Dummies

On 9/29/06, Carlo Stonebanks <stonec(dot)register(at)sympatico(dot)ca> 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