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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

On Wed, May 14, 2008 at 5:24 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.
>
> 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,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

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.

*Robins*

On Wed, May 14, 2008 at 5:24 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.
>
> 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
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

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...-in-one-query/

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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;


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

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.

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Oops!
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:
>
> > 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;
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>