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 59A623B6682 for ; Thu, 16 Dec 2004 17:24:39 +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 62043-05 for ; Thu, 16 Dec 2004 17:24:31 +0000 (GMT) Received: from ct.radiology.uiowa.edu (ct.radiology.uiowa.edu [129.255.60.186]) by svr1.postgresql.org (Postfix) with ESMTP id E8A9D3B64B7 for ; Thu, 16 Dec 2004 17:24:31 +0000 (GMT) Received: from [192.168.1.11] (12-217-241-0.client.mchsi.com [12.217.241.0]) by ct.radiology.uiowa.edu (8.11.6/8.11.6) with ESMTP id iBGHOV320789 for ; Thu, 16 Dec 2004 11:24:31 -0600 Message-ID: <41C1C4CA.4080607@arbash-meinel.com> Date: Thu, 16 Dec 2004 11:24:26 -0600 From: John A Meinel User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 Cc: pgsql-performance@postgresql.org Subject: Re: Improve performance of query References: <1103213467.9908.65.camel@richard> In-Reply-To: <1103213467.9908.65.camel@richard> X-Enigmail-Version: 0.89.5.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enigF1660BBFAFEA877450028FAA" 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: 200412/207 X-Sequence-Number: 9582 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigF1660BBFAFEA877450028FAA Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit The first thing to check... Did you do a recent VACUUM ANALYZE? This updates all the statistics. There are a number of places where it says "rows=1000" which is usually the "I have no idea, let me guess 1000". Also, there are a number of places where the estimates are pretty far off. For instance: Richard Rowell wrote: >-> Subquery Scan "*SELECT* 1" (cost=0.00..64034.15 rows=10540 width=24) (actual time=279.089..4419.371 rows=161 loops=1) > > estimating 10,000 when only 161 is a little bit different. > -> Seq Scan on da_answer a (cost=0.00..63928.75 rows=10540 width=24) (actual time=279.080..4418.808 rows=161 loops=1) > Filter: ((date_effective <= 9999999999::double precision) AND (inactive <> 1) AND (subplan)) > > Though this could be a lack of cross-column statistics. If 2 columns are correlated, the planner isn't as accurate as it could be. Also, date_effective <= 9999999999 doesn't seem very restrictive, could you use a between statement? (date between 0 and 9999999). I know for timestamps usually giving a between is better than a single sided query. This one was underestimated. >-> Subquery Scan "*SELECT* 2" (cost=988627.58..989175.52 rows=2799 width=24) (actual time=290.730..417.720 rows=7556 loops=1) > -> Hash Join (cost=988627.58..989147.53 rows=2799 width=24) (actual time=290.722..395.739 rows=7556 loops=1) > Hash Cond: ("outer".main_id = "inner".uid) > > This is one of the ones that looks like it didn't have any ideas. It could be because of the function. You might consider adding a function index, though I think there are some caveats there. >-> Function Scan on svp_getparentproviderids (cost=0.00..12.50 rows=1000 width=4) (actual time=0.473..0.474 rows=1 loops=1) > > Another very poor estimation. It might be a need to increase the statistics for this column (ALTER TABLE, ALTER COLUMN, SET STATISTICS). IIRC, compared with other db's postgres defaults to a much lower statistics value. Try changing it from 10 (?) to 100 or so. There was a discussion that every column with an index should use higher statistics. >-> Index Scan using in_da_dr_type_provider on da_data_restrict (cost=0.00..145.50 rows=46 width=8) (actual time=0.041..26.627 rows=7280 loops=1) > > I'm not a great optimizer, these are just some first things to look at. Your sort mem seems pretty low to me (considering you have 1GB of RAM). Perhaps you could bump that up to 40MB instead of 4MB. Also, if you run this query twice in a row, is it still slow? (Sometimes it takes a bit of work to get the right indexes loaded into ram, but then it is faster.) Just some guesses, John =:-> --------------enigF1660BBFAFEA877450028FAA 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.2.4 (Cygwin) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBwcTKJdeBCYSNAAMRAkmEAKClhpUHULd2IENkFdzSStbdPH0bxQCdE34l nieGHqeczah1CFyhitKbydw= =08oE -----END PGP SIGNATURE----- --------------enigF1660BBFAFEA877450028FAA--