Sent this to wrong list. -------- Forwarded Message -------- From: Robin Ericsson <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> Profecta HB ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org