From: | Robin Ericsson <robin(dot)ericsson(at)profecta(dot)se> |
---|---|
To: | pgsql-performance(at)postgresql(dot)org |
Subject: | [Fwd: Re: [GENERAL] query problem] |
Date: | 2004-10-13 16:36:50 |
Message-ID: | 1097685410.24018.136.camel@pylver.localhost.nu. |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Sent this to wrong list.
-------- Forwarded Message --------
From: Robin Ericsson <robin(dot)ericsson(at)profecta(dot)se>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] [PERFORM] query problem
Date: Wed, 13 Oct 2004 18:27:20 +0200
On Wed, 2004-10-13 at 18:01 +0200, Robin Ericsson wrote:
> Using exact timestamp makes the query go back as it should in speed (see
> explain below). However I still have the problem using a stored
> procedure or even using the "ago"-example from above.
Well, changing ago() to use timestamp without time zone it goes ok in
the query. This query now takes ~2ms.
SELECT
data.entered,
data.machine_id,
datatemplate_intervals.template_id,
data_values.value
FROM
data, data_values, datatemplate_intervals
WHERE
datatemplate_intervals.id = data_values.template_id AND
data_values.data_id = data.id AND
data.machine_id IN (SELECT machine_id FROM machine_group_xref
WHERE group_id = 1) AND
ago('60 seconds') < data.entered
Using it in this procedure.
select * from get_current_machine_status('60 seconds', 1);
takes ~100s. Maybe there's some obvious wrong I do about it?
CREATE TYPE public.mstatus_holder AS
(entered timestamp,
machine_id int4,
template_id int4,
value varchar);
CREATE OR REPLACE FUNCTION public.get_current_machine_status(interval,
int4)
RETURNS SETOF mstatus_holder AS
'
SELECT
data.entered,
data.machine_id,
datatemplate_intervals.template_id,
data_values.value
FROM
data, data_values, datatemplate_intervals
WHERE
datatemplate_intervals.id = data_values.template_id AND
data_values.data_id = data.id AND
data.machine_id IN (SELECT machine_id FROM machine_group_xref WHERE
group_id = $2) AND
ago($1) < data.entered
'
LANGUAGE 'sql' VOLATILE;
Regards,
Robin
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
--
Robin Ericsson <robin(dot)ericsson(at)profecta(dot)se>
Profecta HB
From | Date | Subject | |
---|---|---|---|
Next Message | Matt Clark | 2004-10-13 16:47:20 | Re: Opteron vs RHAT |
Previous Message | Robin Ericsson | 2004-10-13 16:36:26 | Re: [PERFORM] query problem |