pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Robin Ericsson <robin.ericsson@profecta.se>
To: pgsql-general@postgresql.org
Subject: Re: [PERFORM] query problem
Date: Wed, 13 Oct 2004 18:27:20 +0200
Message-ID: <1097684840.24018.133.camel@pylver.localhost.nu.> (raw)
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.>

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





view thread (9+ messages)  latest in thread

Message-ID: <1097684840.24018.133.camel@pylver.localhost.nu.>
Permalink:  ../1097684840.24018.133.camel@pylver.localhost.nu./
Also on:    postgresql.org/message-id/1097684840.24018.133.camel@pylver.localhost.nu.

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: robin.ericsson@profecta.se, pgsql-general@postgresql.org
  Subject: Re: [PERFORM] query problem
  In-Reply-To: <1097684840.24018.133.camel@pylver.localhost.nu.>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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