On Wed, 18 Jun 2008, Howard Cole wrote:
Out of interest, if I could create a multicolumn index with both the primary key and the fts key (I don't think I can create a multi-column index using GIST with both the email_id and the fts field), would this reduce access to the table due to the primary key being part of the index?
Unfortunately not, since the indexes do not contain information on whether a particular row is visible in your current transaction. Like I said, concurrency control really complicates things!
More importantly, are there other ways that I can improve performance on this? I am guessing that a lot of the problem is that the email table is so big. If I cut out some of the text fields that are not needed in the search and put them in another table, presumably the size of the table will be reduced to a point where it will reduce the number of disk hits and speed the query up.
Good idea. Note that Postgres is already doing this to some extent with TOAST - read http://www.postgresql.org/docs/8.3/interactive/storage-toast.html - unfortunately, there doesn't seem to be an option to always move particular columns out to TOAST. Your idea will produce an even smaller table. However, are email_ids all that you want from the query?
Matthew -- Okay, I'm weird! But I'm saving up to be eccentric. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
