Skip site navigation (1) Skip section navigation (2)

Query Optimization

From: Zach Calvert <zachcalvert(at)hemerasoftware(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Query Optimization
Date: 2009-05-27 14:28:18
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
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?

Thanks a bunch,


pgsql-novice by date

Next:From: Luiz Eduardo Cantanhede NeriDate: 2009-05-27 14:50:29
Subject: Re: Query Optimization
Previous:From: Just E. MailDate: 2009-05-27 14:23:23
Subject: Re: Read data from text file - SOLVED?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group