Re: Performace Optimization for Dummies

> by the way, stats_command_string is a known performance killer that
> iirc was improved in 8.2. just fyi.

This is a handy fact, I will get on this right away.

> bulk load denomalized tables into scratch tables into the postgresql
> database. create indexes appropriate to the nomalization process
> remembering you can index on virtually any expression in postgresql
> (including regex substitution).

> use sql to process the data. if tables are too large to handle with
> monolithic queries, use cursors and/or functions to handle the
> conversion. now you can keep track of progress using pl/pgsql raise
> notice command for example.

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!

Carlo