pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedfaster and faster!!
2+ messages / 2 participants
[nested] [flat]
* faster and faster!!
@ 2001-09-28 14:02 bpalmer <bpalmer@crimelabs.net>
2001-10-02 15:37 ` Re: faster and faster!! Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: bpalmer @ 2001-09-28 14:02 UTC (permalink / raw)
To: pgsql-sql
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-4983
b. palmer, bpalmer@crimelabs.net pgp:crimelabs.net/bpalmer.pgp5
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: faster and faster!!
2001-09-28 14:02 faster and faster!! bpalmer <bpalmer@crimelabs.net>
@ 2001-10-02 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2001-10-02 15:37 UTC (permalink / raw)
To: bpalmer <bpalmer@crimelabs.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2001-10-02 15:37 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-09-28 14:02 faster and faster!! bpalmer <bpalmer@crimelabs.net>
2001-10-02 15:37 ` Tom Lane <tgl@sss.pgh.pa.us>
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