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 \<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 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 |/
