Re: Query Optimization

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(at)hemerasoftware(dot)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?
>
>
>
> Thanks a bunch,
> ZC
>
> --
> Sent via pgsql-novice mailing list (pgsql-novice(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-novice
>