Optimizing select count query which often takes over 10 seconds - Mailing list pgsql-general

Hello,

for a PostgreSQL 8.4.13 database + pgbouncer
on a 32 GB RAM machine with CentOS 6.3 / 64 bit
I use the following settings:

max_connections = 100
shared_buffers = 4096MB
work_mem = 32MB
checkpoint_segments = 32   # to shut up nightly pg_dump
escape_string_warning = off # to shut up Drupal 7.19 warnings
log_min_duration_statement = 10000

And the latter statement always
reports me just 1 command
(besides Drupal which I can't fix):

LOG:  duration: 12590.394 ms  statement:
        select count(id) from (
            select id,
                   row_number() over(partition by yw order by money
desc) as ranking
            from pref_money
        ) x
        where x.ranking = 1 and id='OK471018960997'

This command comes from a PHP-script
of mine which displays "medals" on
a player profile page - meaning how many
times she won a weekly tournament:

# \d pref_money
                        Table "public.pref_money"
 Column |         Type          |                Modifiers
--------+-----------------------+-----------------------------------------
 id     | character varying(32) |
 money  | integer               | not null
 yw     | character(7)          | default to_char(now(), 'IYYY-IW'::text)
Indexes:
    "pref_money_yw_index" btree (yw)
Foreign-key constraints:
    "pref_money_id_fkey" FOREIGN KEY (id) REFERENCES pref_users(id) ON
DELETE CASCADE

Does anybody please have an idea
how could I optimize it or should
I introduce a hourly job and a "medals"
column (that would make my players
stats less "live")?

Here is the EXPLAIN output
(which I hardly understand) for
a player with 9 weekly medals:

# explain analyze select count(id) from (
            select id,
                   row_number() over(partition by yw order by money
desc) as ranking
            from pref_money
        ) x
        where x.ranking = 1 and id='OK452217781481';
                                                                QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=63694.22..63694.23 rows=1 width=82) (actual
time=4520.719..4520.719 rows=1 loops=1)
   ->  Subquery Scan x  (cost=48519.10..63694.19 rows=11 width=82)
(actual time=4470.620..4520.710 rows=6 loops=1)
         Filter: ((x.ranking = 1) AND ((x.id)::text = 'OK452217781481'::text))
         ->  WindowAgg  (cost=48519.10..57190.58 rows=433574 width=26)
(actual time=4293.315..4491.652 rows=429803 loops=1)
               ->  Sort  (cost=48519.10..49603.03 rows=433574
width=26) (actual time=4293.306..4352.544 rows=429803 loops=1)
                     Sort Key: pref_money.yw, pref_money.money
                     Sort Method:  external sort  Disk: 15856kB
                     ->  Seq Scan on pref_money  (cost=0.00..7923.74
rows=433574 width=26) (actual time=0.006..41.907 rows=429803 loops=1)
 Total runtime: 4525.662 ms
(9 rows)

Thank you for any hints
Alex


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general