Re: analyzing query results

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Lonni J Friedman" <netllama(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: analyzing query results
Date: 2007-08-10 22:06:33
Message-ID: 14587.1186783593@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

"Lonni J Friedman" <netllama(at)gmail(dot)com> writes:
> select subtest,os,arch,info FROM ${table} WHERE (SELECT now() -
> interval '24 hours' < date_created::timestamp)='t' AND
> current_status='FAILED' ;

> My problem is that I can't think of a non-trivial way to adjust the
> SQL query so that it will only capture the last time the subtest ran,
> in the event that it ran multiple times during a 24 hour window.

If you don't mind using a nonstandard feature, DISTINCT ON will probably
help you solve this. This example looks pretty close to being the same
as the "get the latest weather reports" example that you'll find in the
PG reference page for SELECT.

BTW, why are you writing the timestamp filter condition in such a
bizarre way? I'd expect to see that query written as

select subtest,os,arch,info FROM ${table}
WHERE
(now() - interval '24 hours') < date_created
AND current_status='FAILED' ;

which would have a fighting chance of using an index on date_created.
The useless sub-SELECT not only eliminates any chance of using an index,
but incurs a fair amount of extra runtime overhead.

regards, tom lane

In response to

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Avinash Lakshman 2007-08-10 22:58:45 Adding columns dynamically to a table
Previous Message Andrew C. Uselton 2007-08-10 22:04:00 Re: pg_dump/pg_dumpall