Re: inconsistend performance

Then this should work:

WHERE ( "afd", "dss", "dat") <= ('fb',13,'2002080719163600' )
ORDER BY "afd" DESC , "dss" DESC , "dat" DESC LIMIT 1;

If you use < intstead of <= you will not get the equivalant result as
your initial query.

You would get something like:

WHERE ( "afd" < 'fb' )
or ( "afd" = 'fb' and "dss" < 13)
or ( "afd" = 'fb' and "dss" = 13 and "dat" < '2002080719163600' )
ORDER BY "afd" DESC , "dss" DESC , "dat" DESC LIMIT 1;

which is not what you want.

JLL

> fredrik chabot wrote:
>
> Jean-Luc Lachance wrote:
>
> > Bonjour Frederik,
> > Seems to me that instead of
> > WHERE ( "afd" = 'fb' and "dss" = 13 and "dat" <
> > '2002080719163600' )
> > or ( "afd" = 'fb' and "dss" < 13 ) or ( "afd" < 'fb' )
> > ORDER BY "afd" DESC , "dss" DESC , "dat" DESC LIMIT 1;
> > you can write:
> > WHERE ( "afd", "dss", "dat") <= ('fb',13,'2002080719163600' )
> > ORDER BY ("afd", "dss", "dat") DESC LIMIT 1;
> > Might not improve things much though...
> >
> Thanks,
>
> 2 things;
>
> The ORDER BY as you wrote it gave an error. "ERROR: parser: parse
> error at or near "DESC""
>
> The WHERE clause did work although not completely as expected. When I
> write ;
>
> WHERE ( "afd", "dss", "dat") < ('fb',13,'2002080719163600' ) ORDER BY
> "afd" DESC , "dss" DESC , "dat" DESC LIMIT 1;
> ---
>
> NOTICE: QUERY PLAN:
>
> Limit (cost=0.00..3.18 rows=1 width=344)
> -> Index Scan Backward using dsrgl_primary on dsrgl
> (cost=0.00..12580.87 rows=3960 width=344)
>
> EXPLAIN
>
> Which is (I think equivalent to my original) it returns an seemingly
> random record instead of the intended one ignoring the ORDER BY or so
> it seems. (it is BTW verry fast)
>
> If i write;
>
> WHERE ( "afd", "dss", "dat") <= ('fb',13,'2002080719163600' ) ORDER BY
> "afd" DESC , "dss" DESC , "dat" DESC LIMIT 2;
>
> NOTICE: QUERY PLAN:
>
> Limit (cost=0.00..6.35 rows=2 width=344)
> -> Index Scan Backward using dsrgl_primary on dsrgl
> (cost=0.00..12580.87 rows=3960 width=344)
>
> EXPLAIN
>
> It returns the exact record (as expected) and the one just before it
> in sequence (as expected).
>
> It is not clear to me why when I use '<' I get a "seemingly" random
> record while it works as expected with '<='.
>