On Tue, Aug 26, 2014 at 09:20:25AM +0200, Gert Burger <gertbur...@gmail.com>
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 <p...@phdru.name> wrote:
> > On Mon, Aug 25, 2014 at 10:22:32AM +0200, Gert Burger <
> > gertbur...@gmail.com> 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/ p...@phdru.name
Programmers don't die, they just GOSUB without RETURN.
------------------------------------------------------------------------------
Slashdot TV.
Video for Nerds. Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
sqlobject-discuss mailing list
sqlobject-discuss@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
