On Wed, May 27, 2009 at 10:50 AM, Luiz Eduardo Cantanhede Neri
<lecneri@gmail.com> wrote:
From what I noticed yout problem is the seq_scan
Seq Scan on score (cost=0.00..2391.17 rows=39954 width=0)
(actual time=0.012..30.760 rows=38571 loops=1)"
You'll always should void scans, table scan, index scan, etc...
On Wed, May 27, 2009 at 11:28 AM, Zach Calvert
<zachcalvert@hemerasoftware.com> wrote:
Sorry for the cross post - but I'm not sure my original posting to the
performance mailing list was the right place to send my question. So,
let me try again at the novice list.
I have a query and I have run
explain analyze
select count(*)
from score
where leaderboardid=35 and score <= 6841 and active
The result is
"Aggregate (cost=2491.06..2491.07 rows=1 width=0) (actual
time=38.878..38.878 rows=1 loops=1)"
" -> Seq Scan on score (cost=0.00..2391.17 rows=39954 width=0)
(actual time=0.012..30.760 rows=38571 loops=1)"
" Filter: (active AND (score <= 6841) AND (leaderboardid = 35))"
"Total runtime: 38.937 ms"
I have an index on score, I have an index on score, leaderboardid, and
active and still it does a sequential scan. I can't seem to figure
out how to create an index that will
turn that "Seq Scan" into an index scan. The biggest problem is that
the query degrades very quickly with a lot more rows and I will be
getting A LOT MORE rows. What can I do to improve the performance of
this query?
Postgresql is aware of the "cost" associated with each query. In the case of a small table with an index that is not very discriminative, it may choose a sequential scan. However, as you add more rows, the index scan may become more effective and may be used instead. One thing to keep in mind is that an index scan is NOT always faster than a sequential scan.
Sean