Skip site navigation (1) Skip section navigation (2)

Re: Performace Optimization for Dummies

From: "Carlo Stonebanks" <stonec(dot)register(at)sympatico(dot)ca>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Performace Optimization for Dummies
Date: 2006-09-29 04:46:54
Message-ID: efi8fi$27in$ (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
> 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!


In response to


pgsql-performance by date

Next:From: Carlo StonebanksDate: 2006-09-29 04:49:31
Subject: Re: Performace Optimization for Dummies
Previous:From: Carlo StonebanksDate: 2006-09-29 04:37:37
Subject: Re: Performace Optimization for Dummies

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group