X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C362032A892 for ; Wed, 13 Oct 2004 17:30:03 +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 49846-09 for ; Wed, 13 Oct 2004 16:29:58 +0000 (GMT) Received: from zaphod.profecta.se (zaphod.profecta.se [193.235.206.49]) by svr1.postgresql.org (Postfix) with ESMTP id 32D8332A6B1 for ; Wed, 13 Oct 2004 17:29:58 +0100 (BST) Received: from localhost (localhost [127.0.0.1]) by zaphod.profecta.se (Postfix) with ESMTP id E187611A3 for ; Wed, 13 Oct 2004 18:29:46 +0200 (CEST) Received: from [192.168.128.80] (ip050.noname4us.com [212.247.87.50]) by zaphod.profecta.se (Postfix) with ESMTP id A3BB71194 for ; Wed, 13 Oct 2004 18:29:46 +0200 (CEST) Subject: [Fwd: Re: [GENERAL] query problem] From: Robin Ericsson To: pgsql-performance@postgresql.org Content-Type: text/plain Organization: Profecta HB Date: Wed, 13 Oct 2004 18:36:50 +0200 Message-Id: <1097685410.24018.136.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/179 X-Sequence-Number: 8655 Sent this to wrong list. -------- Forwarded Message -------- From: Robin Ericsson To: pgsql-general@postgresql.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 Profecta HB