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

 · 

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