Re: [SQLObject] Indexes with primary key

[Gert B. <ger...@gm...>]

Hi

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).

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


On Tue, Aug 26, 2014 at 12:21 AM, Oleg Broytman <ph...@ph...> wrote:

> Hi!
>
> 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.
>
>
> ------------------------------------------------------------------------------
> Slashdot TV.
> Video for Nerds.  Stuff that matters.
> http://tv.slashdot.org/
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>