Re: inconsistend performance

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

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 let 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