Received: from acheron.rime.com.au (root@albatr.lnk.telstra.net [139.130.54.222]) by hub.org (8.10.1/8.10.1) with ESMTP id e8H7g2s38348 for ; Sun, 17 Sep 2000 03:42:02 -0400 (EDT) Received: from oberon (Oberon.rime.com.au [203.8.195.100]) by acheron.rime.com.au (8.9.3/8.9.3) with SMTP id RAA19473 for ; Sun, 17 Sep 2000 17:41:52 +1000 Message-Id: <3.0.5.32.20000917184319.02976b40@mail.rhyme.com.au> X-Sender: pjw@mail.rhyme.com.au X-Mailer: QUALCOMM Windows Eudora Pro Version 3.0.5 (32) Date: Sun, 17 Sep 2000 18:43:19 +1000 To: pgsql-sql@postgresql.org From: Philip Warner Subject: Does optimizer know about 'constant' expressions? Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Archive-Number: 200009/124 It seems that the optimizer does not know about (or calculate values of) constant expressions when determining strategy. Perhaps I am doing something silly, but: select tmax from ping where pingtime > current_timestamp - interval '2 hour' order by pingtime asc limit 30; is very slow, and: select tmax from ping where pingtime > '17-sep-2000 16:40' order by pingtime asc limit 30; works fine. Is this a known issue? --------------------------------------------------------------------- This one is seriosly slow: uptime=# explain verbose select tmax from ping where pingtime > current_timestamp - interval '2 hour' ord er by pingtime asc limit 30; NOTICE: QUERY DUMP: { INDEXSCAN :startup_cost 0.00 :total_cost 53962.69 :rows 84746 :width 12 :state <> :qptargetlist ({ TARG ETENTRY :resdom { RESDOM :resno 1 :restype 700 :restypmod -1 :resname tmax :reskey 0 :reskeyop 0 :ressort groupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 4 :vartype 700 :vartypmod -1 :varlevelsup 0 : varnoold 1 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 1184 :restypmod -1 :resname pi ngtime :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk true } :expr { VAR :varno 1 :varattno 7 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7}}) :qpqual ({ EXPR :typeOid 16 :opType op : oper { OPER :opno 1324 :opid 1157 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 7 :vartype 1184 :var typmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7} { EXPR :typeOid 1184 :opType op :oper { OPER :opno 1 329 :opid 1190 :opresulttype 1184 } :args ({ EXPR :typeOid 1184 :opType func :oper { FUNC :funcid 1191 : functype 1184 :funcisindex false :funcsize 0 :func_fcache @ 0x0 :func_tlist ({ TARGETENTRY :resdom { RES DOM :resno 1 :restype 1184 :restypmod -1 :resname \ :reskey 0 :reskeyop 0 :ressortgroupref 0 :res junk false } :expr { VAR :varno -1 :varattno 1 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold -1 : varoattno 1}}) :func_planlist <>} :args ({ CONST :consttype 25 :constlen -1 :constisnull false :constvalu e 7 [ 7 0 0 0 110 111 119 ] :constbyval false })} { CONST :consttype 1186 :constlen 12 :constisnull fal se :constvalue 12 [ 0 0 0 0 0 32 -68 64 0 0 0 0 ] :constbyval false })})}) :lefttree <> :righttree <> : extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid ( 1852428) :indxqual (<>) :indxqualorig ( <>) :indxorderdir 1 } NOTICE: QUERY PLAN: Index Scan using ping_ix1 on ping (cost=0.00..53962.69 rows=84746 width=12) EXPLAIN --------------------------------------------------------------------- and this one is fine: uptime=# explain verbose select tmax from ping where pingtime > '17-sep-2000 16:40' order by pingtime asc limit 30; NOTICE: QUERY DUMP: { INDEXSCAN :startup_cost 0.00 :total_cost 61.98 :rows 18 :width 12 :state <> :qptargetlist ({ TARGETENTR Y :resdom { RESDOM :resno 1 :restype 700 :restypmod -1 :resname tmax :reskey 0 :reskeyop 0 :ressortgroupr ef 0 :resjunk false } :expr { VAR :varno 1 :varattno 4 :vartype 700 :vartypmod -1 :varlevelsup 0 :varnoo ld 1 :varoattno 4}} { TARGETENTRY :resdom { RESDOM :resno 2 :restype 1184 :restypmod -1 :resname pingtime :reskey 0 :reskeyop 0 :ressortgroupref 1 :resjunk true } :expr { VAR :varno 1 :varattno 7 :vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7}}) :qpqual <> :lefttree <> :righttree <> :extprm ( ) :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid ( 1852428) :indxqual (({ EXPR :typeOid 16 :opTyp e op :oper { OPER :opno 1324 :opid 1157 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 118 4 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7} { CONST :consttype 1184 :constlen 8 :constisnul l false :constvalue 8 [ 0 0 0 0 60 114 117 65 ] :constbyval false })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 1324 :opid 1157 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 7 : vartype 1184 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 7} { CONST :consttype 1184 :constlen 8 :constisnull false :constvalue 8 [ 0 0 0 0 60 114 117 65 ] :constbyval false })})) :indxorderdir 1 } NOTICE: QUERY PLAN: Index Scan using ping_ix1 on ping (cost=0.00..61.98 rows=18 width=12) EXPLAIN ---------------------------------------------------------------- Philip Warner | __---_____ Albatross Consulting Pty. Ltd. |----/ - \ (A.B.N. 75 008 659 498) | /(@) ______---_ Tel: (+61) 0500 83 82 81 | _________ \ Fax: (+61) 0500 83 82 82 | ___________ | Http://www.rhyme.com.au | / \| | --________-- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/