Re: Query Optimization

On Wed, May 27, 2009 at 10:50 AM, Luiz Eduardo Cantanhede Neri <
lecneri(at)gmail(dot)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(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?
>
>
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