Morphed Indexes... 1

[inkhie]

Hello All,

I am bringing a Fox database across to PostgreSQL (pg).

It's reached 2Gb and that's that for Fox

The applications that use the database are mature and are being morphed to support pg over ODBC.

As part of the process, I have been looking at the many indexes on the main data table. The database has been tuned over the years and uses some (sensible AFAICT) performance enhancements which I haven't used or seen before.

Fox seems to be able to create an index on a field that is a 'morphed' version of the data being indexed.

For example...

The definition for the index POSTTOWN is...
cntry_code+ALLTRIM(UPPER(post_town))
and for index JOBID is...
RIGHT(entrycode,5)
and for index UNIQADD is...
cntry_code+UPPER(establishm)+UPPER(post_town)+UPPER(ad_l1)+UPPER(post_code)

Can this sort of 'super meta data' indexing be done with pg indexes? There are 4 million rows in the main data table, so I want to implement any and all speedups that I and anyone else can think up.

Best Regards, Paul.

 

[ceco]

I didn't quite get the example code, but

is it the thing you are looking, something like

CREATE INDEX some_name ON table_name(trim(upper(field_name)), lower(other_field));

(where some_name is the name of the index, table_name -> the table name, and field_name the field_name, other_field -> some other field )

see

http://www.postgresql.org/docs/8.1/interactive/sql-createindex.html

for more details

 

[inkhie]

Hello Ceco,

Yes, you have hit the nail on the head! I must have been missing functionality like this for years - how embarassing I had to learn it from a Fox database

Thank you very much.

Best Regards, Paul.