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 94D0255D73 for ; Wed, 2 Mar 2005 16:57:14 +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 23453-09 for ; Wed, 2 Mar 2005 16:57:09 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 4C7E3550F4 for ; Wed, 2 Mar 2005 16:57:08 +0000 (GMT) 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 j22Gv5hv007816; (envelope-from ) Wed, 2 Mar 2005 10:57:05 -0600 (CST) Received: from [192.168.1.13] (63-230-158-178.cdrr.qwest.net [63.230.158.178]) (authenticated user=jfmeinel) by server11.icaen.uiowa.edu (8.13.2/smtp-serv-1.7) with ESMTP id j22Gv0l7019860 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Wed, 2 Mar 2005 10:57:02 -0600 (CST) Message-ID: <4225F04F.6060305@arbash-meinel.com> Date: Wed, 02 Mar 2005 10:56:47 -0600 From: John Arbash Meinel User-Agent: Mozilla Thunderbird 1.0 (Macintosh/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Ken Egervari Cc: pgsql-performance@postgresql.org Subject: Re: Help with tuning this query References: <001001c51ef4$3849eeb0$6a01a8c0@KEN> In-Reply-To: <001001c51ef4$3849eeb0$6a01a8c0@KEN> 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="------------enig73B7FDBABADFD8DA76398C77" X-Virus-Scanned: ClamAV 0.83/723/Thu Feb 24 05:54:24 2005 clamav-milter version 0.80j 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-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: 200503/28 X-Sequence-Number: 10733 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig73B7FDBABADFD8DA76398C77 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Ken Egervari wrote: > I've tried to use Dan Tow's tuning method and created all the right > indexes from his diagraming method, but the query still performs quite > slow both inside the application and just inside pgadmin III. Can > anyone be kind enough to help me tune it so that it performs better in > postgres? I don't think it's using the right indexes, or maybe > postgres needs special treatment. > First, what version of postgres, and have you run VACUUM ANALYZE recently? Also, please attach the result of running EXPLAIN ANALYZE. (eg, explain analyze select s.* from shipment ...) It's very possible that you don't have up-to-date statistics, which causes postgres to make a bad estimate of what the fastest plan is. Also, if you are using an older version of postgres (like 7.1) you really should upgrade. There are quite a few performance and real bug fixes. > I've converted the below query to SQL from a Hibernate query, so the > syntax is probably not perfect but it's semantics are exactly the > same. I've done so by looking at the source code, but I can't run it > to get the exact SQL since I don't have the database on my home machine. I don't know how to make Hibernate do what you want, but if you change the query to using subselects (not all databases support this, so hibernate might not let you), you can see a performance improvement. Also sometimes using explicit joins can be worse than just letting the query manager figure it out. So something like select s.* from shipment s, carrier_code cc, carrier c, ... where s.carrier_code_id = cc.id and c.id = cc.carrier_id and .... But again, since this is generated from another program (Hibernate), I really don't know how you tell it how to tune the SQL. Probably the biggest "non-bug" performance improvements are from tuning the SQL. But if postgres isn't using the right indexes, etc, you can probably fix that. John =:-> > > select s.* > from shipment s > inner join carrier_code cc on s.carrier_code_id = cc.id > inner join carrier c on cc.carrier_id = c.id > inner join carrier_to_person ctp on ctp.carrier_id = c.id > inner join person p on p.id = ctp.person_id > inner join shipment_status cs on s.current_status_id = cs.id > inner join release_code rc on cs.release_code_id = rc.id > left join shipment_status ss on ss.shipment_id = s.id > where > p.id = :personId and > s.is_purged = false and > rc.number = '9' and > cs is not null and > cs.date >= current_date - 31 > order by cs.date desc > Just assume I have no indexes for the moment because while some of the > indexes I made make it work faster, it's still around 250 milliseconds > and under heavy load, the query performs very badly (6-7 seconds). > > For your information: > > shipment contains 40,000 rows > shipment_status contains 80,000 rows > release_code contains 8 rows > person contains 300 rows > carrier contains 60 rows > carrier_code contains 70 rows > > The filter ratios are: > > rc.number = '9' (0.125) > cs.date >= current_date - 31 (.10) > p.id = ? (0.003) > s.is_purged = false (.98) > > I really hope someone can help since I'm pretty much stuck. > > Best regards and many thanks, > Ken --------------enig73B7FDBABADFD8DA76398C77 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 iD8DBQFCJfBRJdeBCYSNAAMRAhd8AJ0Za9S7yjBOyJBbrsnC20QCb10DNwCeKXD7 oZWgu4d7mmEYg2+9iUYzzY0= =5kTN -----END PGP SIGNATURE----- --------------enig73B7FDBABADFD8DA76398C77--