X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 0590DD1C4BF for ; Fri, 8 Aug 2003 11:48:14 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 13704-09 for ; Fri, 8 Aug 2003 08:46:49 -0300 (ADT) Received: from wight.ymogen.net (unknown [217.27.240.153]) by svr1.postgresql.org (Postfix) with SMTP id E0080D1C4B9 for ; Fri, 8 Aug 2003 08:48:01 -0300 (ADT) Received: (qmail 12824 invoked from network); 8 Aug 2003 11:48:04 -0000 Received: from unknown (HELO solent) (213.165.136.12) by wight.ymogen.net with SMTP; 8 Aug 2003 11:48:04 -0000 From: "Matt Clark" To: Subject: Cost estimates consistently too high - does it matter? Date: Fri, 8 Aug 2003 12:47:20 +0100 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_008C_01C35DAB.35269E60" X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2911.0) Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200308/112 X-Sequence-Number: 9835 This is a multi-part message in MIME format. ------=_NextPart_000_008C_01C35DAB.35269E60 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Hi, I've noticed that the cost estimates for a lot of my queries are consistently far to high. Sometimes it's because the row estimates are wrong, like this: explain analyze select logtime from loginlog where uid='Ymogen::YM_User::3e2c0869c2fdd26d8a74d218d5a6ff585d490560' and result = 'Success' order by logtime desc limit 3; NOTICE: QUERY PLAN: Limit (cost=0.00..221.85 rows=3 width=8) (actual time=0.21..2.39 rows=3 loops=1) -> Index Scan Backward using loginlog_logtime_idx on loginlog (cost=0.00..12846.69 rows=174 width=8) (actual time=0.20..2.37 rows=4 loops=1) Total runtime: 2.48 msec The row estimate here is off by a factor of 50, but the cost estimate is off by a factor of 5000. Sometimes the row estimates are good, but the costs are still too high: explain analyze select u.email from ym_user u join mobilepm m on (m.ownerid = u._id) where m.status = 'Validated' and m.network = 'TMOBILEUK'; NOTICE: QUERY PLAN: Nested Loop (cost=0.00..2569.13 rows=441 width=145) (actual time=1.93..248.57 rows=553 loops=1) -> Seq Scan on mobilepm m (cost=0.00..795.11 rows=441 width=58) (actual time=1.69..132.83 rows=553 loops=1) -> Index Scan using ym_user_id_idx on ym_user u (cost=0.00..4.01 rows=1 width=87) (actual time=0.19..0.20 rows=1 loops=553) Total runtime: 249.47 msec loginlog has 180000 rows, mobilepm has 12000, ym_user has 50000, and they've all been analyzed prior to running the query. The server is a Quad PIII 700 Xeon/1MB cache, 3GB RAM, hardware RAID 10 on two SCSI channels with 128MB write-back cache. I've lowered the random_page_cost to 2 to reflect the decent disk IO, but I suppose the fact that the DB & indexes are essentially all cached in RAM might also be affecting the results, although effective_cache_size is set to a realistic 262144 (2GB). Those planner params in full: #effective_cache_size = 1000 # default in 8k pages #random_page_cost = 4 #cpu_tuple_cost = 0.01 #cpu_index_tuple_cost = 0.001 #cpu_operator_cost = 0.0025 effective_cache_size = 262144 # 2GB of FS cache random_page_cost = 2 For now the planner seems to be making the right choices, but my concern is that at some point the planner might start making some bad decisions, especially on more complex queries. Should I bother tweaking the planner costs more, and if so which ones? Am I fretting over nothing? Cheers Matt Matt Clark Ymogen Ltd matt@ymogen.net corp.ymogen.net ------=_NextPart_000_008C_01C35DAB.35269E60 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi,
 
I've not= iced that=20 the cost estimates for a lot of my queries are consistently far to high.&nb= sp;=20 Sometimes it's because the row estimates are wrong, like=20 this:
 
explain = analyze=20 select logtime from loginlog where=20 uid=3D'Ymogen::YM_User::3e2c0869c2fdd26d8a74d218d5a6ff585d490560' and resul= t =3D=20 'Success' order by logtime desc limit 3;
NOTICE:  QUERY=20 PLAN:
Limit&nb= sp;=20 (cost=3D0.00..221.85 rows=3D3 width=3D8) (actual time=3D0.21..2.39 rows=3D3= =20 loops=3D1)
  ->  Index Scan Backward using loginlog_logtime= _idx on=20 loginlog  (cost=3D0.00..12846.69 rows=3D174 width=3D8) (actual time=3D= 0.20..2.37=20 rows=3D4 loops=3D1)
Total runtime: 2.48 msec
 
The row = estimate=20 here is off by a factor of 50, but the cost estimate is off by a factor=20 of 5000.
 
Sometime= s the row=20 estimates are good, but the costs are still too high:
 
explain = analyze=20 select u.email from ym_user u join mobilepm m on (m.ownerid =3D u._id) wher= e=20 m.status =3D 'Validated' and m.network =3D 'TMOBILEUK';
NOTICE:  QU= ERY=20 PLAN:
Nested L= oop =20 (cost=3D0.00..2569.13 rows=3D441 width=3D145) (actual time=3D1.93..248.57 r= ows=3D553=20 loops=3D1)
  ->  Seq Scan on mobilepm m  (cost=3D0.00.= .795.11=20 rows=3D441 width=3D58) (actual time=3D1.69..132.83 rows=3D553 loops=3D1) =20 ->  Index Scan using ym_user_id_idx on ym_user u  (cost=3D0.00= ..4.01=20 rows=3D1 width=3D87) (actual time=3D0.19..0.20 rows=3D1 loops=3D553)
Tot= al runtime:=20 249.47 msec
 
loginlog= has 180000=20 rows, mobilepm has 12000, ym_user has 50000, and they've all been analyzed = prior=20 to running the query.
 
The serv= er is a=20 Quad PIII 700 Xeon/1MB cache, 3GB RAM, hardware RAID 10 on two=20 SCSI channels with 128MB write-back cache.
 
I've low= ered the=20 random_page_cost to 2 to reflect the decent disk IO, but I suppose the fact= that=20 the DB & indexes are essentially all cached in RAM might also be affect= ing=20 the results, although effective_cache_size is set to a realistic 262144=20 (2GB).  Those planner params in full:
 
#effective_cache_size =3D 1000  # default in 8k=20 pages
#random_page_cost =3D 4
#cpu_tuple_cost =3D=20 0.01
#cpu_index_tuple_cost =3D 0.001
#cpu_operator_cost =3D=20 0.0025
effective_cache_size =3D 262144 # 2GB of FS cache
random_page_= cost =3D=20 2
 
For now the planner seems to be making the right cho= ices, but=20 my concern is that at some point the planner might start making some bad=20 decisions, especially on more complex queries.  Should I bother tweaki= ng=20 the planner costs more, and if so which ones?  Am I fretting over=20 nothing?
 
Cheers
 
Matt

Matt Clark
Ymogen Ltd
matt@ymogen.net
corp.ymogen.net

 
------=_NextPart_000_008C_01C35DAB.35269E60--