Thread: faster and faster!!

Two questions:

- Does the order of WHERE clauses make a difference?  I was trying to make
some of my code faster and tried switching two clauses that should have
effected the query,  but there was no speed difference.

- I have a query:

SELECT stamp
FROM table
WHERE timestamp > now() - INTERVAL '1 0:00'

This query takes 13 seconds to run.  If I were to run another query such
as:

WHERE sid > 1000

it would return almost instantly.  There are indexes on both columns.  Is
dealing with timestamps that slow?  Is there any way to speed it up?

Thanks all,
- Brandon


----------------------------------------------------------------------------c: 646-456-5455
              h: 201-798-4983b. palmer,  bpalmer@crimelabs.net           pgp:crimelabs.net/bpalmer.pgp5
 

bpalmer <bpalmer@crimelabs.net> writes:
> - I have a query:
> SELECT stamp
> FROM table
> WHERE
>   timestamp > now() - INTERVAL '1 0:00'
> This query takes 13 seconds to run.

It's probably not being indexed (use EXPLAIN to check).  The problem
is that "now() - INTERVAL '1 0:00'" is not considered a constant, so
the planner can't use an indexscan.

The usual workaround is to make a function like so:

tgl=# create function ago(interval) returns timestamp as
tgl-# 'select now() - $1' language 'sql' with (iscachable);
CREATE
tgl=# select ago('1 0:00');           ago
---------------------------2001-10-01 11:31:13.62-04
(1 row)

and then writeWHERE timestamp > ago('1 0:00');

The "iscachable" attribute of the function tells the planner it's okay
to reduce the function call to a constant during planning, and then it
becomes possible to use an indexscan.  Strictly speaking, marking this
function iscachable is a lie, but it works just fine in interactive
queries.  (You might have trouble with it if you tried to put such
a query into a plpgsql function; you'd probably find that the function
call gets reduced sooner than you want.)

See past discussions of this issue in the archives --- a search for
"iscachable" should turn up some threads.  In the long term we probably
need an intermediate concept between "cachable" and "not cachable",
along the lines of "result is constant within a query", so that the
behavior of now() can be described more accurately.
        regards, tom lane