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 2D5E653A82 for ; Thu, 7 Apr 2005 16:37:01 +0100 (BST) 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 14205-03 for ; Thu, 7 Apr 2005 15:36:59 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 0B0B553878 for ; Thu, 7 Apr 2005 16:36:57 +0100 (BST) Received: from server11.icaen.uiowa.edu (server11.icaen.uiowa.edu [128.255.17.51]) by server07.icaen.uiowa.edu (8.13.3/8.12.9) with ESMTP id j37Fauhi012210; (envelope-from ) Thu, 7 Apr 2005 10:36:56 -0500 (CDT) Received: from [192.168.1.13] (12-215-118-172.client.mchsi.com [12.215.118.172]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j37FasSW004787 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Thu, 7 Apr 2005 10:36:55 -0500 (CDT) Message-ID: <42555396.40303@arbash-meinel.com> Date: Thu, 07 Apr 2005 10:36:54 -0500 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Joel Fradkin Cc: PostgreSQL Perform Subject: Re: Any way to speed this up? References: <005301c53b84$6b83f8a0$797ba8c0@jfradkin> In-Reply-To: <005301c53b84$6b83f8a0$797ba8c0@jfradkin> X-Enigmail-Version: 0.89.6.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigE92CC557C2C75A7DCC73D018" X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Scanned: ClamAV version 0.83, clamav-milter version 0.83 on clamav.icaen.uiowa.edu X-Virus-Status: Clean X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200504/158 X-Sequence-Number: 11555 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigE92CC557C2C75A7DCC73D018 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Joel Fradkin wrote: > Running this explain on windows box, but production on linux both 8.0.1 > > The MSSQL is beating me out for some reason on this query. > > The linux box is much more powerful, I may have to increase the cache, > but I am pretty sure its not an issue yet. > > It has 8 gig internal memory any recommendation on the cache size to use? > > > > explain analyze select * from viwassoclist where clientnum = 'SAKS' > > > > "Merge Join (cost=59871.79..60855.42 rows=7934 width=112) (actual > time=46906.000..48217.000 rows=159959 loops=1)" > The first thing I noticed was this. Notice that the estimated rows is 8k, the actual rows is 160k. Which means the planner is mis-estimating the selectivity of your merge. > " -> Sort (cost=59478.03..59909.58 rows=172618 width=75) (actual > time=46844.000..46985.000 rows=159960 loops=1)" > > " Sort Key: a.locationid" > This sort actually isn't taking very long. It starts at 46800 and runs until 47000 so it takes < 1 second. > " -> Merge Right Join (cost=0.00..39739.84 rows=172618 > width=75) (actual time=250.000..43657.000 rows=176431 loops=1)" > > " Merge Cond: ((("outer".clientnum)::text = > ("inner".clientnum)::text) AND ("outer".id = "inner".jobtitleid))" > > " -> Index Scan using ix_tbljobtitle_id on tbljobtitle > jt (cost=0.00..194.63 rows=6391 width=37) (actual > time=32.000..313.000 rows=5689 loops=1)" > > " Filter: (1 = presentationid)" > > " -> Index Scan using ix_tblassoc_jobtitleid on > tblassociate a (cost=0.00..38218.08 rows=172618 width=53) (actual > time=31.000..41876.000 rows=176431 loops=1)" > > " Index Cond: ((clientnum)::text = 'SAKS'::text)" > This is where the actual expense is. The merge right join starts at 250, and runs until 43000. Which seems to be caused primarily by the index scan of tblassociate. How many rows are in tblassociate? I'm assuming quite a bit, since the planner thinks an index scan is faster than seq scan for 170k rows. (If you have > 2M this is probably accurate) I don't really know how long this should take, but 38s for 172k rows seems a little long. John =:-> --------------enigE92CC557C2C75A7DCC73D018 Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.0 (Darwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCVVOWJdeBCYSNAAMRAh30AJ96NXUlHGAZMzjGi5kznKLtrVwYygCeI+F5 9OjWBaFJagb253/q9Mukqps= =BhLL -----END PGP SIGNATURE----- --------------enigE92CC557C2C75A7DCC73D018--