X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 947733A3ED8 for ; Wed, 17 Nov 2004 16:08:45 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 18194-06 for ; Wed, 17 Nov 2004 16:08:28 +0000 (GMT) Received: from gizmo08ps.bigpond.com (gizmo08ps.bigpond.com [144.140.71.18]) by svr1.postgresql.org (Postfix) with SMTP id B694D3A3ECC for ; Wed, 17 Nov 2004 16:08:24 +0000 (GMT) Received: (qmail 14677 invoked from network); 17 Nov 2004 16:08:17 -0000 Received: from unknown (HELO psmam13.bigpond.com) (144.135.25.106) by gizmo08ps.bigpond.com with SMTP; 17 Nov 2004 16:08:17 -0000 Received: from cpe-141-168-103-177.qld.bigpond.net.au ([141.168.103.177]) by psmam13.bigpond.com(MAM REL_3_4_2a 243/5174672) with SMTP id 5174672; Thu, 18 Nov 2004 02:08:17 +1000 Date: Thu, 18 Nov 2004 02:08:33 +0000 From: David Brown To: pgsql-performance@postgresql.org Subject: Analyzer is clueless X-Mailer: David Brown's registered AK-Mail 3.2 [eng] Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Message-Id: <20041117160824.B694D3A3ECC@svr1.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.9 tagged_above=0.0 required=5.0 tests=DATE_IN_FUTURE_06_12 X-Spam-Level: * X-Archive-Number: 200411/253 X-Sequence-Number: 9207 I'm doing some performance profiling with a simple two-table query: SELECT L."ProductID", sum(L."Amount") FROM "drinv" H JOIN "drinvln" L ON L."OrderNo" =3D H."OrderNo" WHERE ("OrderDate" between '2003-01-01' AND '2003-04-30') GROUP BY L."ProductID" drinv and drinvln have about 100,000 and 3,500,000 rows respectively. Actu= al data size in the large table is 500-600MB. OrderNo is indexed in both t= ables, as is OrderDate. The environment is PGSQL 8 on Win2k with 512MB RAM (results are similar to= 7.3 from Mammoth). I've tried tweaking various conf parameters, but apart= from using up memory, nothing seems to have had a tangible effect - the A= nalyzer doesn't seem to take resources into account like some of the doco = suggests. The date selection represents about 5% of the range. Here's the plan summa= ries: Three months (2003-01-01 to 2003-03-30) =3D 1 second HashAggregate (cost=3D119365.53..119368.74 rows=3D642 width=3D26) -> Nested Loop (cost=3D0.00..118791.66 rows=3D114774 width=3D26) -> Index Scan using "drinv_OrderDate" on drinv h (cost=3D0.00..2= 00.27 rows=3D3142 width=3D8) Index Cond: (("OrderDate" >=3D '2003-01-01'::date) AND ("Ord= erDate" <=3D '2003-03-30'::date)) -> Index Scan using "drinvln_OrderNo" on drinvln l (cost=3D0.00.= =2E28.73 rows=3D721 width=3D34) Index Cond: (l."OrderNo" =3D "outer"."OrderNo") Four months (2003-01-01 to 2003-04-30) =3D 60 seconds HashAggregate (cost=3D126110.53..126113.74 rows=3D642 width=3D26) -> Hash Join (cost=3D277.55..125344.88 rows=3D153130 width=3D26) Hash Cond: ("outer"."OrderNo" =3D "inner"."OrderNo") -> Seq Scan on drinvln l (cost=3D0.00..106671.35 rows=3D3372935 = width=3D34) -> Hash (cost=3D267.07..267.07 rows=3D4192 width=3D8) -> Index Scan using "drinv_OrderDate" on drinv h (cost=3D0= =2E00..267.07 rows=3D4192 width=3D8) Index Cond: (("OrderDate" >=3D '2003-01-01'::date) AND= ("OrderDate" <=3D '2003-04-30'::date)) Four months (2003-01-01 to 2003-04-30) with Seq_scan disabled =3D 75 secon= ds HashAggregate (cost=3D130565.83..130569.04 rows=3D642 width=3D26) -> Merge Join (cost=3D519.29..129800.18 rows=3D153130 width=3D26) Merge Cond: ("outer"."OrderNo" =3D "inner"."OrderNo") -> Sort (cost=3D519.29..529.77 rows=3D4192 width=3D8) Sort Key: h."OrderNo" -> Index Scan using "drinv_OrderDate" on drinv h (cost=3D0= =2E00..267.07 rows=3D4192 width=3D8) Index Cond: (("OrderDate" >=3D '2003-01-01'::date) AND= ("OrderDate" <=3D '2003-04-30'::date)) -> Index Scan using "drinvln_OrderNo" on drinvln l (cost=3D0.00.= =2E119296.29 rows=3D3372935 width=3D34) Statistics were run on each table before query execution. The random page = cost was lowered to 2, but as you can see, the estimated costs are wild an= yway. As a comparison, MS SQL Server took less than 15 seconds, or 4 times faste= r. MySQL (InnoDB) took 2 seconds, which is 30 times faster. The query looks straightforward to me (it might be clearer with a subselec= t), so what on earth is wrong?