Re: Compound Indexes

On 15/08/07, Chris Browne <cbbrowne(at)acm(dot)org> wrote:
> phoenix(dot)kiula(at)gmail(dot)com ("Phoenix Kiula") writes:
> > I have a table with ten columns. My queries basically one column as
> > the first WHERE condition, so an index on that column is certain. But
> > the columns after that one vary depending on end-user's choice (this
> > is a reporting application) and so does the sorting order.
> >
> > In MySQL world, I had sort_buffer in the config file, and I made a
> > compound index with the columns most often used in these types of
> > queries. So my index looked like:
> >
> > INDEX idx_trades(id, t_id, c_id, s_id, t_brief, created_on);
> >
> > This has five columns in it. While reading the pgsql documentation, I
> > gather than anything beyond three columns offers diminishing benefits.
> >
> > My queries will look like these:
> >
> > SELECT * from trades where id = 99999
> > and c_id = 9999
> > ORDER by s_id;
> >
> > SELECT * from trades where id = 99999
> > and s_id = 99990
> > ORDER by created_on desc ;
> >
> > SELECT * from trades where id = 99999
> > and s_id = 99990
> > and t_brief ~* 'more|than|one|word'
> > ORDER by created_on desc ;
> >
> > So my question: how does PGSQL optimize its sorts? If I were to index
> > merely the columns that are most commonly used in the reporting WHERE
> > clause, would that be ok? Some ofthese columns may be "TEXT" type --
> > how should I include these in the index (in MySQL, I included only the
> > first 100 words in the index).
>
> If you have only these three sorts of queries, then I would speculate
> that the following indices *might* be useful:
>
> create idx1 on trades (id);
> create idx2 on trades (c_id);
> create idx3 on trades (s_id);
> create idx4 on trades (created_on);
> create idx5 on trades (created_on) where t_brief ~* 'more|than|one|word';
> create idx6 on trades (id, s_id) where t_brief ~* 'more|than|one|word';
>
> (I'm assuming with idx5 and idx6 that you were actually searching for
> 'more|than|one|word'; if what is searched for can vary, then idx5/idx6
> are worthless.)
>
> You could try adding them all, and check out which of them are
> actually used by the query planner. And eventually drop out the
> irrelevant ones.
>
> PostgreSQL has a rather sophisticated query planner (pretty much
> "rocket science," compared to MySQL), and it is even possible that it
> would use multiple of those indices simultaneously for some of the
> queries. Which indexes, if any, it will use will vary from query to
> query based on the parameters in the query.

Thank you so much! My only concern, probably a hangover from the MySQL
world, is that if I have 5 of 6 indices, what would that do to INSERT
and UPDATE performance if all these indices have to be updated? Is
updating individual indices faster than one large compound index?