DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:MIME-Version:Content-Type:Message-ID;
	b=NmDx1TWZKDhprO3ebKKzi+NqHI10j+RkxXH5HZ5PoBpJXR0LjBFeOBqnheOQLbZFjeelXHwb8YwshJDv/A6Uyq79BTazmyVoHMSw/aE/ZP+6Q3Ii1KL+Bmu9sTmmfeSCwpOLedSufQEjY+VL8COF4Z4lGbVD8Qx0P4GhfJsH43Y=;
Date: Wed, 30 May 2007 08:56:45 -0700 (PDT)
From: Trigve Siver <trigves@yahoo.com>
Subject: slow query execution
To: pgsql-sql@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=ascii
Message-ID: <543741.9839.qm@web52709.mail.re2.yahoo.com>

Hi all,

This query executes very slow:

select (select count(*) from customer where id <= a.id) as row, id, from customer as a order by id;

Where customer has id column and others and has also index on id column. The table has about 10.000+ records. When used with "explain", it gives me this output:

                                        QUERY PLAN

-----------------------------------------------------------------------------------------
 Sort  (cost=6513774.23..6513801.37 rows=10855 width=14)
   Sort Key: id
   ->  Seq Scan on customer a  (cost=0.00..6513046.62 rows=10855 width=14)
         SubPlan
           ->  Aggregate  (cost=599.94..599.95 rows=1 width=0)
                 ->  Bitmap Heap Scan on customer  (cost=28.66..590.89 rows=361 width=0)
                       Recheck Cond: (id <= $0)
                       ->  Bitmap Index Scan on iid  (cost=0.00..28.66 rows=361 width=0)
                             Index Cond: (id <= $0)
(9 riadkov)


So is there any solution to speed up this query? Or can I get the same result but with some other query?

thanks

Trigve


____________________________________________________________________________________
Moody friends. Drama queens. Your life? Nope! - their life, your story. Play Sims Stories at Yahoo! Games.
http://sims.yahoo.com/