X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id B620432C231 for ; Wed, 13 Oct 2004 17:20:41 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 49116-01 for ; Wed, 13 Oct 2004 16:20:31 +0000 (GMT) Received: from zaphod.profecta.se (profecta1.cust.morotsmedia.se [193.235.206.49]) by svr1.postgresql.org (Postfix) with ESMTP id 265F232C223 for ; Wed, 13 Oct 2004 17:20:29 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by zaphod.profecta.se (Postfix) with ESMTP id A8C9011A4 for ; Wed, 13 Oct 2004 18:20:17 +0200 (CEST) Received: from [192.168.128.80] (ip050.noname4us.com [212.247.87.50]) by zaphod.profecta.se (Postfix) with ESMTP id 67E7611A3 for ; Wed, 13 Oct 2004 18:20:17 +0200 (CEST) Subject: Re: [PERFORM] query problem From: Robin Ericsson To: pgsql-general@postgresql.org In-Reply-To: <1097683270.24018.124.camel@pylver.localhost.nu.> References: <1097659271.24018.68.camel@pylver.localhost.nu.> <14684.1097679793@sss.pgh.pa.us> <1097683270.24018.124.camel@pylver.localhost.nu.> Content-Type: text/plain Organization: Profecta HB Date: Wed, 13 Oct 2004 18:27:20 +0200 Message-Id: <1097684840.24018.133.camel@pylver.localhost.nu.> Mime-Version: 1.0 X-Mailer: Evolution 2.0.0 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/550 X-Sequence-Number: 67074 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