Обсуждение: calculated expressions and index use

Hi all,

I use simple sql statement like this

select * from foo
where created_at >= 'now'::timestamp - '1 hour'::interval;

My table is indexed on created_at field. The query above doesn't use it, 
but if I use

select * from foo where created_at >= 'now'

the index is used.

It looks like if the engine finds some expression to be evaluated it 
gets rid of any index use (due the possible dependency?).

Is it possible to do something to 'precompute' some value in the query 
condition, so planner understand is as a constant value?

(... and I've got some reason NOT to use a parameter in my Perl DBI code...)

Maybe my conclusions are wrong, of course.

Thank you very much in advance

Pavel

-- 
Pavel Hlavnicka
Ginger Alliance
www.gingerall.com

A very dirty work around could be:
select * from foo where created+at >= (select now() - '1 hour'::interval);

Or perhaps this already works:
select * from foo where created+at >= (now() - '1 hour'::interval);

Pavel Hlavnicka wrote:
> Hi all,
> 
> I use simple sql statement like this
> 
> select * from foo
> where created_at >= 'now'::timestamp - '1 hour'::interval;
> 
> My table is indexed on created_at field. The query above doesn't use it, 
> but if I use
> 
> select * from foo where created_at >= 'now'
> 
> the index is used.
> 
> It looks like if the engine finds some expression to be evaluated it 
> gets rid of any index use (due the possible dependency?).
> 
> Is it possible to do something to 'precompute' some value in the query 
> condition, so planner understand is as a constant value?
> 
> (... and I've got some reason NOT to use a parameter in my Perl DBI code...)
> 
> Maybe my conclusions are wrong, of course.
> 
> Thank you very much in advance
> 
> Pavel
> 

Pavel Hlavnicka <pavel@gingerall.cz> writes:
> select * from foo
> where created_at >= 'now'::timestamp - '1 hour'::interval;

> My table is indexed on created_at field. The query above doesn't use it, 

What's your PG version?  In 7.2 and later that expression will be folded
to a constant.
        regards, tom lane

I use 7.2.1. :(

Do you thing my considerations on constant vs. expression are correct? I 
tried different syntax, no success.

Thanks

Pavel

Tom Lane wrote:
> Pavel Hlavnicka <pavel@gingerall.cz> writes:
> 
>>select * from foo
>>where created_at >= 'now'::timestamp - '1 hour'::interval;
> 
> 
>>My table is indexed on created_at field. The query above doesn't use it, 
> 
> 
> What's your PG version?  In 7.2 and later that expression will be folded
> to a constant.
> 
>             regards, tom lane

-- 
Pavel Hlavnicka
Ginger Alliance
www.gingerall.com

Yes, you are right. It was an issue reated to the type compatibility. My 
braindead script created tables with 'datetime' types, what is 
'timestamp without time zone' after I switched to 'timestamp', all works 
fine.

Thanks a loc, the fact, you ensured me, that the constant expression 
should be recognized, pushed me much further.

Thanks again

Have a nice day

Pavel

Tom Lane wrote:
> Pavel Hlavnicka <pavel@gingerall.cz> writes:
> 
>>I use 7.2.1. :(
> 
> 
> 7.2 certainly will fold "'now'::timestamp - '1 hour'::interval" to a
> timestamp constant.  This could be a datatype compatibility issue
> (is created_at the same type?  in particular, with/without time zone?)
> or it could be a selectivity issue --- ie, the planner might think the
> condition covers too much of the table to make the index useful.
> 
> It would be useful to look at the results of EXPLAIN ANALYZE for this
> query, with and without ENABLE_SEQSCAN turned off.
> 
>             regards, tom lane

-- 
Pavel Hlavnicka
Ginger Alliance
www.gingerall.com

Pavel Hlavnicka <pavel@gingerall.cz> writes:
> I use 7.2.1. :(

7.2 certainly will fold "'now'::timestamp - '1 hour'::interval" to a
timestamp constant.  This could be a datatype compatibility issue
(is created_at the same type?  in particular, with/without time zone?)
or it could be a selectivity issue --- ie, the planner might think the
condition covers too much of the table to make the index useful.

It would be useful to look at the results of EXPLAIN ANALYZE for this
query, with and without ENABLE_SEQSCAN turned off.
        regards, tom lane