shared_buffers = 32768 max_fsm_relations = 500 max_fsm_pages = 1000000 sort_mem = 16384 vacuum_mem = 131072 effective_cache_size = 327680 random_page_cost = 1.25 cpu_tuple_cost = 0.005 cpu_index_tuple_cost = 0.001 cpu_operator_cost = 0.0001 cu2nl=> \d auth Table "public.auth" Column | Type | Modifiers -------------+--------------------------+------------------------------------------------------ id | integer | not null default nextval('public.auth_id_seq'::text) nick | character varying(16) | not null password | character varying(16) | not null email | character varying(64) | not null emailnew | character varying(64) | level | smallint | not null default 0 karma | smallint | not null default 127 datemailed | timestamp with time zone | datesmsed | timestamp with time zone | status | smallint | not null default 0 statusinfo | character varying(16) | statusdate | timestamp with time zone | gsmnr | bigint | gsmoperator | smallint | not null default 0 gsmstatus | smallint | not null default 255 Indexes: auth_pkey primary key btree (id), auth_gsmnr_key unique btree (gsmnr), auth_nick_key unique btree (nick), auth_nick_lower_key unique btree (lower(nick)), auth_email_lower_key btree (lower(email)), auth_email_strrevlower_key btree (strrevlower(email)), auth_password_lower_key btree (lower("password")) Triggers: counter_users cu2nl=> \d reportuser Table "public.reportuser" Column | Type | Modifiers --------------+--------------------------+--------------------- idreporter | integer | not null idtarget | integer | not null status | smallint | not null default 0 datecreated | timestamp with time zone | datemodified | timestamp with time zone | datelocked | timestamp with time zone | reason | character varying(255) | not null default '' Indexes: reportuser_datecreated_key btree (datecreated), reportuser_datecreated_status_3_key btree (datecreated) WHERE (status < 3), reportuser_datecreated_status_5_key btree (datecreated) WHERE (status < 5), reportuser_idreporter_key btree (idreporter), reportuser_idtarget_key btree (idtarget), reportuser_status_key btree (status) Triggers: counter_reportuser_status cu2nl=> EXPLAIN ANALYZE SELECT id, nick, karma, level, COUNT(*) AS count, (karma-128)*COUNT(*) AS x FROM reportuser, auth WHERE auth.id=reportuser.idreporter GROUP BY idreporter, auth.id, auth.nick, auth.karma, auth.level ORDER BY x DESC, count DESC, karma DESC LIMIT 11; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=15433.50..15433.50 rows=11 width=26) (actual time=6008.18..6008.20 rows=11 loops=1) -> Sort (cost=15433.50..15433.86 rows=3536 width=26) (actual time=6008.18..6008.19 rows=12 loops=1) Sort Key: ((auth.karma - 128) * count(*)), count(*), auth.karma -> Aggregate (cost=15396.87..15425.16 rows=3536 width=26) (actual time=5574.60..5867.46 rows=9940 loops=1) -> Group (cost=15396.87..15418.09 rows=35365 width=26) (actual time=5574.55..5763.71 rows=30148 loops=1) -> Sort (cost=15396.87..15400.41 rows=35365 width=26) (actual time=5574.53..5595.51 rows=30148 loops=1) Sort Key: reportuser.idreporter, auth.id, auth.nick, auth.karma, auth."level" -> Merge Join (cost=800.70..15290.00 rows=35365 width=26) (actual time=243.95..5289.00 rows=30148 loops=1) Merge Cond: ("outer".id = "inner".idreporter) -> Index Scan using auth_pkey on auth (cost=0.00..14407.97 rows=607336 width=22) (actual time=0.04..3923.71 rows=605168 loops=1) -> Sort (cost=800.70..804.23 rows=35365 width=4) (actual time=243.86..269.37 rows=35417 loops=1) Sort Key: reportuser.idreporter -> Seq Scan on reportuser (cost=0.00..693.83 rows=35365 width=4) (actual time=0.01..95.13 rows=35417 loops=1) Total runtime: 6026.86 msec (14 rows) cu2nl=> SET enable_mergejoin TO off; SET cu2nl=> EXPLAIN ANALYZE SELECT id, nick, karma, level, COUNT(*) AS count, (karma-128)*COUNT(*) AS x FROM reportuser, auth WHERE auth.id=reportuser.idreporter GROUP BY idreporter, auth.id, auth.nick, auth.karma, auth.level ORDER BY x DESC, count DESC, karma DESC LIMIT 11; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=88698.95..88698.96 rows=11 width=26) (actual time=11920.93..11920.95 rows=11 loops=1) -> Sort (cost=88698.95..88699.31 rows=3536 width=26) (actual time=11920.92..11920.93 rows=12 loops=1) Sort Key: ((auth.karma - 128) * count(*)), count(*), auth.karma -> Aggregate (cost=88662.32..88690.62 rows=3536 width=26) (actual time=11486.97..11780.27 rows=9940 loops=1) -> Group (cost=88662.32..88683.54 rows=35365 width=26) (actual time=11486.91..11676.86 rows=30148 loops=1) -> Sort (cost=88662.32..88665.86 rows=35365 width=26) (actual time=11486.88..11507.77 rows=30148 loops=1) Sort Key: reportuser.idreporter, auth.id, auth.nick, auth.karma, auth."level" -> Hash Join (cost=83859.05..88555.45 rows=35365 width=26) (actual time=3745.74..11100.43 rows=30148 loops=1) Hash Cond: ("outer".idreporter = "inner".id) -> Seq Scan on reportuser (cost=0.00..693.83 rows=35365 width=4) (actual time=0.01..89.23 rows=35417 loops=1) -> Hash (cost=11835.68..11835.68 rows=607336 width=22) (actual time=3745.25..3745.25 rows=0 loops=1) -> Seq Scan on auth (cost=0.00..11835.68 rows=607336 width=22) (actual time=0.03..2438.54 rows=607566 loops=1) Total runtime: 11931.21 msec (13 rows) cu2nl=> SET enable_hashjoin TO off; SET cu2nl=> EXPLAIN ANALYZE SELECT id, nick, karma, level, COUNT(*) AS count, (karma-128)*COUNT(*) AS x FROM reportuser, auth WHERE auth.id=reportuser.idreporter GROUP BY idreporter, auth.id, auth.nick, auth.karma, auth.level ORDER BY x DESC, count DESC, karma DESC LIMIT 11; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=109688.85..109688.85 rows=11 width=26) (actual time=1831.55..1831.57 rows=11 loops=1) -> Sort (cost=109688.85..109689.21 rows=3536 width=26) (actual time=1831.54..1831.55 rows=12 loops=1) Sort Key: ((auth.karma - 128) * count(*)), count(*), auth.karma -> Aggregate (cost=109652.22..109680.51 rows=3536 width=26) (actual time=1397.77..1691.03 rows=9940 loops=1) -> Group (cost=109652.22..109673.44 rows=35365 width=26) (actual time=1397.69..1587.60 rows=30148 loops=1) -> Sort (cost=109652.22..109655.76 rows=35365 width=26) (actual time=1397.67..1418.60 rows=30148 loops=1) Sort Key: reportuser.idreporter, auth.id, auth.nick, auth.karma, auth."level" -> Nested Loop (cost=0.00..109545.35 rows=35365 width=26) (actual time=0.07..997.10 rows=30148 loops=1) -> Seq Scan on reportuser (cost=0.00..693.83 rows=35365 width=4) (actual time=0.02..112.22 rows=35417 loops=1) -> Index Scan using auth_pkey on auth (cost=0.00..3.07 rows=1 width=22) (actual time=0.02..0.02 rows=1 loops=35417) Index Cond: (auth.id = "outer".idreporter) Total runtime: 1841.93 msec (12 rows)