pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: bpalmer <bpalmer@crimelabs.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: faster and faster!!
Date: Tue, 02 Oct 2001 11:37:58 -0400
Message-ID: <9041.1002037078@sss.pgh.pa.us> (raw)
In-Reply-To: <Pine.BSO.4.33.0109280959130.992-100000@mizer.crimelabs.net>
References: <Pine.BSO.4.33.0109280959130.992-100000@mizer.crimelabs.net>

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 write
	WHERE 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



view thread (2+ messages)

Message-ID: <9041.1002037078@sss.pgh.pa.us>
Permalink:  ../9041.1002037078@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/9041.1002037078@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, bpalmer@crimelabs.net
  Subject: Re: faster and faster!!
  In-Reply-To: <9041.1002037078@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox