pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
faster 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