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 '<='.

JLL

fredrik chabot wrote:

Hello experts,

Abstract;

If I select 1 record on the primary key I know exists postgresql is very
fast, selecting the previous or next record (with limit 1)  is horible.

Given this table:

CREATE TABLE dsrgl ("f6lock" int4, "afd" varchar(  4), "dss" int4, "dat"
varchar( 16), "srt" int4, "akt" varchar( 12), "oms" varchar( 20), "anc"
int4, "vkk" float8, "vkka" float8, "vkkt" float8, "vkkv" varchar(  4),
"vkkr" float8, "fav" varchar( 12), "ibs" int4, "nkk" float8, "nkkv"
varchar(  4), "nkkr" float8, "vko" float8, "vkoa" float8, "vkot" float8,
"vkov" varchar(  4), "vkor" float8, "faa" varchar( 12), "vbs" int4,
"sta" int4, "nko" float8, "nkov" varchar(  4), "nkor" float8, "stb"
int4, "stc" int4, "std" int4, "ste" int4, "fsa" int4, "fsb" int4, "fsc"
int4, "fsd" int4, "fse" int4,
CONSTRAINT dsrgl_primary PRIMARY KEY("afd","dss","dat"));

Insert +/- 700000 rows and then:

SELECT "afd", "dss", "dat",
 "srt", "akt", "oms", "anc", "vkk", "vkka",
"vkkt", "vkkv", "vkkr", "fav", "ibs", "nkk", "nkkv", "nkkr", "vko",
"vkoa", "vkot", "vkov", "vkor", "faa", "vbs", "sta", "nko", "nkov",
"nkor", "stb", "stc", "std", "ste", "fsa", "fsb", "fsc", "fsd", "fse",
"usr", "dtv", "dti" FROM dsrgl
WHERE ( "afd" = 'fb' and "dss" =  13 and "dat" = '20020712143411' ) ;

takes approx 0.000939 seconds

SELECT "afd", "dss", "dat", "srt", "akt", "oms", "anc", "vkk", "vkka",
"vkkt", "vkkv", "vkkr", "fav", "ibs", "nkk", "nkkv", "nkkr", "vko",
"vkoa", "vkot", "vkov", "vkor", "faa", "vbs", "sta", "nko", "nkov",
"nkor", "stb", "stc", "std", "ste", "fsa", "fsb", "fsc", "fsd", "fse",
"usr", "dtv", "dti" FROM dsrgl
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;

takes approx  7.048736 seconds

If I l
et postgresql explain it to me:

NOTICE:  QUERY PLAN:

Limit  (cost=0.00..910.68 rows=100 width=344)
  ->  Index Scan Backward using dsrgl_primary on dsrgl
(cost=0.00..325691.57 rows=35764 width=344)

EXPLAIN

Is it something I'm doing wrong or doesn't postgresql optimizer
understand my select and if so is there something I can do so it will
understand?

thanks,

fredrik chabot

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org