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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
