Thread: Add a ROWCOUNT to the output of a select.
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; -Gavin
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
<span style="font-family: verdana,sans-serif;">While we could always check for the query performance reasons, I rather thinkthat this is an overkill for the purpose of mere line numbers.</span><br style="font-family: verdana,sans-serif;" /><brstyle="font-family: verdana,sans-serif;" /><span style="font-family: verdana,sans-serif;">If such queries don't changefrequently, you could be better off using a simple function that instead adds a 'rownumber' field to the output ofthe inner SQL query. The 'rownumber' could instead be calculated by simply incrementing it within a FOR loop for each row.</span><brstyle="font-family: verdana,sans-serif;" /><br style="font-family: verdana,sans-serif;" /><b style="font-family:verdana,sans-serif;">Robins</b><br /><br /><div class="gmail_quote">On Wed, May 14, 2008 at 5:24 AM, Gavin'Beau' Baumanis <<a href="mailto:gavinb@eclinic.com.au" target="_blank">gavinb@eclinic.com.au</a>> wrote:<br /><blockquoteclass="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 themailing list I found a post that provided me with a query that does just what I need.<br /><br /> However, the query takesFOREVER and although this is stated in the original mail I thought I would ask if there was any advice that you mightbe able to provide to speed things up a little.<br /><br /> 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!<br /><br /> Please find the query below - and of course - thanksin advance for any 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 /><br /> -Gavin<br /><font color="#888888"><br/> -- <br /> Sent via pgsql-sql mailing list (<a href="mailto:pgsql-sql@postgresql.org" target="_blank">pgsql-sql@postgresql.org</a>)<br/> To make changes to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-sql"target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br /></font></blockquote></div><br/>
On Wed, May 14, 2008 at 1:54 AM, Gavin 'Beau' Baumanis <gavinb@eclinic.com.au> 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. I think that this should be helpful: http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulative-sum-in-one-query/
In article <36af4bed0805131818p147bb440oa4c1944939e3b313@mail.gmail.com>,
"Robins Tharakan" <tharakan@gmail.com> writes:
> While we could always check for the query performance reasons, I
> rather think that this is an overkill for the purpose of mere line
> numbers.
> If such queries don't change frequently, you could be better off
> using a simple function that instead adds a 'rownumber' field to the
> output of the inner SQL query. The 'rownumber' could instead be
> calculated by simply incrementing it within a FOR loop for each row.
I think a sequence is much simpler:
create temp sequence tmp;
select nextval('tmp') as rownum, contactdate
from myTable
where contactdate > '2007-06-30 23:59:59'
order by contactdate;
On Wed, May 14, 2008 at 10:40 AM, Harald Fuchs
<hari.fuchs@googlemail.com> wrote:
> I think a sequence is much simpler:
>
> create temp sequence tmp;
> select nextval('tmp') as rownum,
> contactdate
> from
> myTable
> where
> contactdate > '2007-06-30 23:59:59'
> order by
> contactdate;
I used to do it this way myself, but the solution in my previous post
is really worth the trouble.
Oops!
Of course, I meant a sequence.
Robins
Of course, I meant a sequence.
Robins
On Wed, May 14, 2008 at 2:10 PM, Harald Fuchs <hari.fuchs@googlemail.com> wrote:
In article <36af4bed0805131818p147bb440oa4c1944939e3b313@mail.gmail.com>,"Robins Tharakan" <tharakan@gmail.com> writes:I think a sequence is much simpler:
> While we could always check for the query performance reasons, I
> rather think that this is an overkill for the purpose of mere line
> numbers.
> If such queries don't change frequently, you could be better off
> using a simple function that instead adds a 'rownumber' field to the
> output of the inner SQL query. The 'rownumber' could instead be
> calculated by simply incrementing it within a FOR loop for each row.
create temp sequence tmp;
select nextval('tmp') as rownum,contactdate
from
myTable
where
contactdate > '2007-06-30 23:59:59'
order by
contactdate;--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql