Thread
-
inconsistend performance
fredrik chabot <fredrik@f6.nl> — 2002-08-20T19:05:17Z
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 -
Re: inconsistend performance
Jean-Luc Lachance <jllachan@nsd.ca> — 2002-08-20T19:51:54Z
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 -
Re: inconsistend performance
fredrik chabot <fredrik@f6.nl> — 2002-08-20T21:36:25Z
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <head> <meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1"> <title></title> </head> <body> Jean-Luc Lachance wrote:<br> <blockquote type="cite" cite="mid3D629DDA.AC0B9AA2@nsd.ca"> <pre wrap="">Bonjour Frederik,<br><br>Seems to me that instead of<br><br> WHERE ( "afd" = 'fb' and "dss" = 13 and "dat" < '2002080719163600' )<br> or ( "afd" = 'fb' and "dss" < 13 ) or ( "afd" < 'fb' )<br> ORDER BY "afd" DESC , "dss" DESC , "dat" DESC LIMIT 1;<br><br>you can write:<br><br> WHERE ( "afd", "dss", "dat") <= ('fb',13,'2002080719163600' )<br> ORDER BY ("afd", "dss", "dat") DESC LIMIT 1;<br><br>Might not improve things much though...</pre> </blockquote> Thanks,<br> <br> 2 things;<br> <br> The ORDER BY as you wrote it gave an error. "<tt>ERROR: parser: parse error at or near "DESC"</tt>"<br> <br> The WHERE clause did work although not completely as expected. When I write ;<br> <br> <tt>WHERE ( "afd", "dss", "dat") < ('fb',13,'2002080719163600' ) ORDER BY "afd" DESC , "dss" DESC , "dat" DESC LIMIT 1;</tt><br> ---<br> <br> <tt>NOTICE: QUERY PLAN:<br> <br> Limit (cost=0.00..3.18 rows=1 width=344)<br> -> Index Scan Backward using dsrgl_primary on dsrgl (cost=0.00..12580.87 rows=3960 width=344)<br> <br> EXPLAIN<br> </tt><br> 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)<br> <br> If i write;<br> <br> <tt>WHERE ( "afd", "dss", "dat") <= ('fb',13,'2002080719163600' ) ORDER BY "afd" DESC , "dss" DESC , "dat" DESC LIMIT 2;</tt><br> <br> <tt>NOTICE: QUERY PLAN:<br> <br> Limit (cost=0.00..6.35 rows=2 width=344)<br> -> Index Scan Backward using dsrgl_primary on dsrgl (cost=0.00..12580.87 rows=3960 width=344)<br> <br> EXPLAIN</tt><br> <br> It returns the exact record (as expected) and the one just before it in sequence (as expected). <br> <br> It is not clear to me why when I use '<' I get a "seemingly" random record while it works as expected with '<='.<br> <blockquote type="cite" cite="mid3D629DDA.AC0B9AA2@nsd.ca"> <pre wrap=""><br><br>JLL<br><br></pre> <pre wrap=""><!---->fredrik chabot wrote:<br></pre> <blockquote type="cite"> <pre wrap="">Hello experts,<br><br>Abstract;<br><br>If I select 1 record on the primary key I know exists postgresql is very<br>fast, selecting the previous or next record (with limit 1) is horible.<br><br>Given this table:<br><br>CREATE TABLE dsrgl ("f6lock" int4, "afd" varchar( 4), "dss" int4, "dat"<br>varchar( 16), "srt" int4, "akt" varchar( 12), "oms" varchar( 20), "anc"<br>int4, "vkk" float8, "vkka" float8, "vkkt" float8, "vkkv" varchar( 4),<br>"vkkr" float8, "fav" varchar( 12), "ibs" int4, "nkk" float8, "nkkv"<br>varchar( 4), "nkkr" float8, "vko" float8, "vkoa" float8, "vkot" float8,<br>"vkov" varchar( 4), "vkor" float8, "faa" varchar( 12), "vbs" int4,<br>"sta" int4, "nko" float8, "nkov" varchar( 4), "nkor" float8, "stb"<br>int4, "stc" int4, "std" int4, "ste" int4, "fsa" int4, "fsb" int4, "fsc"<br>int4, "fsd" int4, "fse" int4,<br>CONSTRAINT dsrgl_primary PRIMARY KEY("afd","dss","dat"));<br><br>Insert +/- 700000 rows and then:<br><br>SELECT "afd", "dss", "dat", "srt", "akt", "oms", "anc", "vkk", "vkka",<br>"vkkt", "vkkv", "vkkr", "fav", "ibs", "nkk", "nkkv", "nkkr", "vko",<br>"vkoa", "vkot", "vkov", "vkor", "faa", "vbs", "sta", "nko", "nkov",<br>"nkor", "stb", "stc", "std", "ste", "fsa", "fsb", "fsc", "fsd", "fse",<br>"usr", "dtv", "dti" FROM dsrgl<br>WHERE ( "afd" = 'fb' and "dss" = 13 and "dat" = '20020712143411' ) ;<br><br>takes approx 0.000939 seconds<br><br>SELECT "afd", "dss", "dat", "srt", "akt", "oms", "anc", "vkk", "vkka",<br>"vkkt", "vkkv", "vkkr", "fav", "ibs", "nkk", "nkkv", "nkkr", "vko",<br>"vkoa", "vkot", "vkov", "vkor", "faa", "vbs", "sta", "nko", "nkov",<br>"nkor", "stb", "stc", "std", "ste", "fsa", "fsb", "fsc", "fsd", "fse",<br>"usr", "dtv", "dti" FROM dsrgl<br>WHERE ( "afd" = 'fb' and "dss" = 13 and "dat" < '2002080719163600' )<br>or ( "afd" = 'fb' and "dss" < 13 ) or ( "afd" < 'fb' )<br>ORDER BY "afd" DESC , "dss" DESC , "dat" DESC LIMIT 1;<br><br>takes approx 7.048736 seconds<br><br>If I l et postgresql explain it to me:<br><br>NOTICE: QUERY PLAN:<br><br>Limit (cost=0.00..910.68 rows=100 width=344)<br> -> Index Scan Backward using dsrgl_primary on dsrgl<br>(cost=0.00..325691.57 rows=35764 width=344)<br><br>EXPLAIN<br><br>Is it something I'm doing wrong or doesn't postgresql optimizer<br>understand my select and if so is there something I can do so it will<br>understand?<br><br>thanks,<br><br>fredrik chabot<br><br>---------------------------(end of broadcast)---------------------------<br>TIP 5: Have you checked our extensive FAQ?<br><br><a class="moz-txt-link-freetext" href="http://www.postgresql.org/users-lounge/docs/faq.html">http://www.postgresql.org/users-lounge/docs/faq.html</a><br></pre> </blockquote> <pre wrap=""><!----><br>---------------------------(end of broadcast)---------------------------<br>TIP 6: Have you searched our list archives?<br><br><a class="moz-txt-link-freetext" href="http://archives.postgresql.org">http://archives.postgresql.org</a><br></pre> </blockquote> <br> </body> </html> -
Re: inconsistend performance
Jean-Luc Lachance <jllachan@nsd.ca> — 2002-08-21T14:12:33Z
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 '<='. >