pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed From: Vincent van Leeuwen <pgsql.spam@vinz.nl>
To: pgsql-performance@postgresql.org
Subject: tweaking costs to favor nestloop
Date: Wed, 11 Jun 2003 21:33:14 +0200
Message-ID: <20030611193313.GE1535@md2.mediadesign.nl> (raw )
Hi,
I'm running PG 7.3.2 on a dual P3 1 GHz, 4GB RAM, 5-disk RAID 5 (hardware) on
Debian Linux, kernel 2.4.21-rc3.
I'm unable to tweak the various _cost settings in such a way that attached
query will use the right plan. Attachment contains relevant config file
settings, table defenitions and explain analyze output with some enable_*
settings turned off.
Could anyone tell me what I'm doing wrong in the query itself or tell me what
I should do with which config file setting to let the planner choose the
fastest plan by itself?
Regards,
Vincent van Leeuwen
Media Design - http://www.mediadesign.nl/
Attachments:
nestloop (text/plain, 8.5K) download
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)
view thread (5+ messages) latest in thread
Message-ID: <20030611193313.GE1535@md2.mediadesign.nl>
Permalink: ../20030611193313.GE1535@md2.mediadesign.nl/
Also on: postgresql.org/message-id/20030611193313.GE1535@md2.mediadesign.nl
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: pgsql.spam@vinz.nl
Subject: Re: tweaking costs to favor nestloop
In-Reply-To: <20030611193313.GE1535@md2.mediadesign.nl>
References: <20030611193313.GE1535@md2.mediadesign.nl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox