Does optimizer know about 'constant' expressions? - Mailing list pgsql-sql

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 :vartype1184 :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 \<noname> :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 asclimit 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 :typeOid16  :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   |/