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 1328B550EE for ; Fri, 4 Mar 2005 00:22:34 +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 45264-06 for ; Fri, 4 Mar 2005 00:22:24 +0000 (GMT) Received: from server07.icaen.uiowa.edu (server07.icaen.uiowa.edu [128.255.17.47]) by svr1.postgresql.org (Postfix) with ESMTP id 89AFF54431 for ; Fri, 4 Mar 2005 00:22:21 +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 j240MM4h010851; (envelope-from ) Thu, 3 Mar 2005 18:22:22 -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 j240MI3G006638 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256); (envelope-from ) Thu, 3 Mar 2005 18:22:20 -0600 (CST) Message-ID: <4227AA36.2080700@arbash-meinel.com> Date: Thu, 03 Mar 2005 18:22:14 -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: Josh Berkus , pgsql-performance@postgresql.org Subject: Re: Help with tuning this query (with explain analyze finally) References: <001001c51ef4$3849eeb0$6a01a8c0@KEN> <200503022152.13211.josh@agliodbs.com> <004601c51fbe$828bbb50$6a01a8c0@KEN> <200503030935.14537.josh@agliodbs.com> <001d01c5204a$b440edf0$6a01a8c0@KEN> In-Reply-To: <001d01c5204a$b440edf0$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="------------enigEF41C74903B51B1B829BFBCA" 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/68 X-Sequence-Number: 10773 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enigEF41C74903B51B1B829BFBCA Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Ken Egervari wrote: > Josh, > > I did everything you said and my query does perform a bit better. > I've been getting speeds from 203 to 219 to 234 milliseconds now. I > tried increasing the work mem and the effective cache size from the > values you provided, but I didn't see any more improvement. I've > tried to looking into setting the shared buffers for Windows XP, but > I'm not sure how to do it. I'm looking in the manual at: > http://www.postgresql.org/docs/8.0/interactive/kernel-resources.html#SYSVIPC-PARAMETERS > > You probably don't need to change anything for Windows. If you set shared_buffers too high, then postgres won't start. If it is starting, then you don't need to modify the OS to get more shared buffers. (For instance, on my Mac, I can't get shared_buffers > 500 without changing things, but on windows I run with 3000 and no modification). > It doesn't mention windows at all. Does anyone have any ideas on have > to fix this? > Do you need the interior sort? It's taking ~93ms to get 7k rows from shipment_status, and then another 30ms to sort them. This isn't a lot, so it might be fine. Also, did you ever try CLUSTER current_status_date_idx ON shipment_status. This groups the rows in shipment_status by their status date, which helps put items with the same date next to eachother. This may effect other portions of the query, or other queries. Also, if you are inserting sequentially, it would seem that the items would already be naturally near eachother based on date. The next big cost is having to merge the 28k rows with the fast hash plan, which takes about 80ms. I guess the biggest issue is that you are doing a lot of work, and it takes time to do it. Also, I've noticed that this query is being run with exactly the same data. Which is good to compare two methods. But remember to test on multiple potential values. You might be better off one way with this query, but much worse for a different dataset. I noticed that this seems to have fewer rows than what postgres thinks the *average* number would be. (It predicts 60k and you only get 28k rows). If this query is performed a lot, and you can be okay with a slight delay in updating, you could always switch to some sort of lazy materialized view. You could also always throw more hardware at it. :) If the shipment_status is one of the bottlenecks, create a 4-disk raid10 and move the table over. I don't remember what your hardware is, but I don't remember it being a quad opteron with 16GB ram, and 20 15k SCSI disks, with the transaction log on a solid state disk. :) Why do you need the query to be 30ms? ~250ms is still pretty fast. If you are needing updates faster than that, you might look more into *why* and then handle it from a higher level. And naturally, the most important this is to test it under load. 250ms is pretty good, but if under load it goes back to 6s, then we probably should look for different alternatives. Also, what is the load that is causing the problem? Is it that you have some other big seqscans which are causing all of your tables to go out of cache? Also, I believe I remember you saying that your production server is a P4, is that a single P4? Because I know postgres prefers Opterons to Pentium Xeons when in a multiprocessor machine. Look through the archives about spinlocks and the context switch bug. (context storm, etc). Plus, since opterons are 64-bit, you can throw a lot more RAM at them. I believe opterons outperform xeons for the same cost, *and* you can scale them up with extra ram. But remember, the biggest bottleneck is almost *always* the I/O. So put more & faster disks into the system first. John =:-> > Here is the new explain analyze. > > Sort (cost=1996.21..1996.26 rows=17 width=165) (actual > time=297.000..297.000 rows=39 loops=1) > Sort Key: ss.date > -> Merge Right Join (cost=1951.26..1995.87 rows=17 width=165) > (actual time=297.000..297.000 rows=39 loops=1) > Merge Cond: ("outer".id = "inner".driver_id) > -> Index Scan using driver_pkey on driver d (cost=0.00..42.16 > rows=922 width=43) (actual time=0.000..0.000 rows=922 loops=1) > -> Sort (cost=1951.26..1951.30 rows=17 width=122) (actual > time=297.000..297.000 rows=39 loops=1) > Sort Key: s.driver_id > -> Hash Join (cost=586.48..1950.91 rows=17 width=122) > (actual time=219.000..297.000 rows=39 loops=1) > Hash Cond: ("outer".carrier_code_id = "inner".id) > -> Merge Join (cost=571.97..1931.95 rows=830 > width=87) (actual time=219.000..297.000 rows=310 loops=1) > Merge Cond: ("outer".current_status_id = > "inner".id) > -> Index Scan using > shipment_current_status_id_idx on shipment s (cost=0.00..2701.26 > rows=60307 width=66) (actual time=0.000..62.000 rows=27711 loops=1) > Filter: (is_purged = false) > -> Sort (cost=571.97..576.38 rows=1766 > width=21) (actual time=125.000..156.000 rows=6902 loops=1) > Sort Key: ss.id > -> Hash Join (cost=1.11..476.72 > rows=1766 width=21) (actual time=0.000..93.000 rows=6902 loops=1) > Hash Cond: > ("outer".release_code_id = "inner".id) > -> Index Scan Backward using > current_status_date_idx on shipment_status ss (cost=0.00..387.35 > rows=14122 width=21) (actual time=0.000..16.000 rows=14925 loops=1) > Index Cond: (date >= > (('now'::text)::date - 31)) > -> Hash (cost=1.10..1.10 rows=1 > width=4) (actual time=0.000..0.000 rows=0 loops=1) > -> Seq Scan on > release_code rc (cost=0.00..1.10 rows=1 width=4) (actual > time=0.000..0.000 rows=1 loops=1) > Filter: > ((number)::text = '9'::text) > -> Hash (cost=14.51..14.51 rows=2 width=35) > (actual time=0.000..0.000 rows=0 loops=1) > -> Nested Loop (cost=4.92..14.51 rows=2 > width=35) (actual time=0.000..0.000 rows=2 loops=1) > -> Index Scan using person_pkey on > person p (cost=0.00..5.73 rows=1 width=4) (actual time=0.000..0.000 > rows=1 loops=1) > Index Cond: (id = 355) > -> Hash Join (cost=4.92..8.75 rows=2 > width=39) (actual time=0.000..0.000 rows=2 loops=1) > Hash Cond: ("outer".id = > "inner".carrier_id) > -> Seq Scan on carrier c > (cost=0.00..3.54 rows=54 width=4) (actual time=0.000..0.000 rows=54 > loops=1) > -> Hash (cost=4.92..4.92 rows=2 > width=43) (actual time=0.000..0.000 rows=0 loops=1) > -> Hash Join > (cost=3.04..4.92 rows=2 width=43) (actual time=0.000..0.000 rows=2 > loops=1) > Hash Cond: > ("outer".carrier_id = "inner".carrier_id) > -> Seq Scan on > carrier_code cc (cost=0.00..1.57 rows=57 width=35) (actual > time=0.000..0.000 rows=57 loops=1) > -> Hash > (cost=3.04..3.04 rows=1 width=8) (actual time=0.000..0.000 rows=0 > loops=1) > -> Index Scan > using carrier_to_person_person_id_idx on carrier_to_person ctp > (cost=0.00..3.04 rows=1 width=8) (actual time=0.000..0.000 rows=1 > loops=1) > Index > Cond: (355 = person_id) > Total runtime: 297.000 ms > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly --------------enigEF41C74903B51B1B829BFBCA 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 iD8DBQFCJ6o6JdeBCYSNAAMRAtRvAJ46n974ZPYYdmE0GFdLtjTnGNbAHQCgrJ1g 6437Q0gq2jau5gmHmxY7HjU= =Jruy -----END PGP SIGNATURE----- --------------enigEF41C74903B51B1B829BFBCA--