Re: inconsistend performance - Mailing list pgsql-bugs

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
LIMIT1;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 veryfast, selecting the
previousor 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 dsrglWHERE ( "afd" = 'fb' and "dss" =  13 and "dat" = '20020712143411' ) ;takes
approx0.000939 secondsSELECT "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 dsrglWHERE ( "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 secondsIf I l 
et postgresql explain it to me:NOTICE:  QUERY PLAN:Limit  (cost=0.00..910.68 rows=100 width=344)  ->  Index Scan
Backwardusing dsrgl_primary on dsrgl(cost=0.00..325691.57 rows=35764 width=344)EXPLAINIs it something I'm doing wrong
ordoesn't postgresql optimizerunderstand my select and if so is there something I can do so it
willunderstand?thanks,fredrikchabot---------------------------(end of broadcast)---------------------------TIP 5: Have
youchecked 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