Re: [SQLObject] Indexes with primary key

On Tue, Aug 26, 2014 at 09:20:25AM +0200, Gert Burger <ger...@gm...> wrote:
> We have some queries which needs to find the oldest/latest rows which match
> certain conditions and having the id field as the last column in an index
> provides us with huge performance gains(Due to significantly less random
> reads of old data).
> 
> Something like: 'SELECT id FROM table WHERE cond1 AND cond2 AND cond3 ORDER
> BY id DESC LIMIT 1;'
> 
> Without the id column( index(col1, col2, col3) ) Postgres needs to fetch
> all the matching rows before it can sort them and return the result. With
> the id column( index(col1, col2, col3, id) ) in the index and as the last
> column in the index it allows Postgres to 'instantly' lookup the first/last
> id for a set of conditions without fetching all the rows and sorting
> them(Since they would already be sorted in the index).

   I see now.

> Do you any suggestions on how I can work around this limitation?

   Either patch SQLObject or create an index at the SQL level; you don't
need to do everything through SQLObject.

> On Tue, Aug 26, 2014 at 12:21 AM, Oleg Broytman <ph...@ph...> wrote:
> > On Mon, Aug 25, 2014 at 10:22:32AM +0200, Gert Burger <
> > ger...@gm...> wrote:
> > > Is it possible to create indexes using SQLO that include the current
> > > 'Table's primary key?
> > > eg DatabaseIndex(col1, col2, col3, 'id')
> > >
> > > Currently SQLO is complaining that the column doesn't exist.
> >
> >    It seems you're right -- SQLObject creates indices that can only
> > include explicitly declared columns, and id is and implicit column.
> >
> >    On the other hand why do you want to create and index with id at all?
> > Isn't an index with a unique non-null column equivalent to index with
> > exactly that column?

Oleg.
-- 
     Oleg Broytman            http://phdru.name/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.