Re: inconsistend performance
От | fredrik chabot |
---|---|
Тема | Re: inconsistend performance |
Дата | |
Msg-id | 3D62B659.8070000@f6.nl обсуждение исходный текст |
Ответ на | inconsistend performance (fredrik chabot <fredrik@f6.nl>) |
Список | 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
В списке pgsql-bugs по дате отправления: