Thread: 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