On Wed, May 14, 2008 at 5:24 AM, Gavin 'Beau' Baumanis < [EMAIL PROTECTED]> wrote:
> Hi Everyone, > > After spending some time searching through our good friend Mr. Google and > the mailing list I found a post that provided me with a query that does just > what I need. > > However, the query takes FOREVER and although this is stated in the > original mail I thought I would ask if there was any advice that you might > be able to provide to speed things up a little. > > And while the query does return over 27,000 rows in my case, I didn't > expect it to take 9 minutes and 11 seconds! > > Please find the query below - and of course - thanks in advance for any > assistance you might be able to provide me with! > > select > (select > count(*) > from > myTable as myCount > where > myCount.contactdate <= myTable.contactdate > ) as rownum, > contactdate > from > myTable > where > contactdate > '2007-06-30 23:59:59' > order by > contactdate; > > <http://www.postgresql.org/mailpref/pgsql-sql> Posting EXPLAIN command's output would have helped here. This sub-query in the SELECT list is a correlated sub-query, so it will be executed for each row that passes the oouter query's WHERE clause. If you don't have it already, I'd suggest creating an index on the 'contactdate' column; that should help speed up the query. In absence of such an index, the planner will choose Sequential Scan, which is very expensive/time-consuming. Best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com Mail sent from my BlackLaptop device
