From: Trigve Siver <trigves@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: slow query execution
Date: Wed, 30 May 2007 08:56:45 -0700 (PDT)
Message-ID: <543741.9839.qm@web52709.mail.re2.yahoo.com> (raw)
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: trigves@yahoo.com
Subject: Re: slow query execution
In-Reply-To: <543741.9839.qm@web52709.mail.re2.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox