Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group