On Wed, May 14, 2008 at 5:24 AM, Gavin 'Beau' Baumanis <<a
href="mailto:gavinb@eclinic.com.au">gavinb@eclinic.com.au</a>>wrote:<br /><div class="gmail_quote"><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> Hi
Everyone,<br/><br /> After spending some time searching through our good friend Mr. Google and the mailing list I found
apost that provided me with a query that does just what I need.<br /><br /> However, the query takes FOREVER and
althoughthis is stated in the original mail I thought I would ask if there was any advice that you might be able to
provideto speed things up a little.<br /><br /> And while the query does return over 27,000 rows in my case, I didn't
expectit to take 9 minutes and 11 seconds!<br /><br /> Please find the query below - and of course - thanks in advance
forany assistance you might be able to provide me with!<br /><br /> select<br /> (select<br /> count(*)<br
/> from<br /> myTable as myCount<br /> where<br /> myCount.contactdate <=
myTable.contactdate<br/> ) as rownum,<br /> contactdate<br /> from<br /> myTable<br /> where<br />
contactdate> '2007-06-30 23:59:59'<br /> order by<br /> contactdate;<br /><br /><font color="#888888"><a
href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank"></a></font></blockquote></div><br />Posting EXPLAIN
command'soutput would have helped here.<br /><br />This sub-query in the SELECT list is a correlated sub-query, so it
willbe executed for each row that passes the oouter query's WHERE clause. If you don't have it already, I'd suggest
creatingan index on the 'contactdate' column; that should help speed up the query. In absence of such an index, the
plannerwill choose Sequential Scan, which is very expensive/time-consuming.<br /><br />Best regards,<br />-- <br
/>gurjeet[.singh]@EnterpriseDB.com<br/>singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com<br /><br
/>EnterpriseDB<a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br /><br />Mail sent from my
BlackLaptopdevice
