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 2F40932A24E for ; Wed, 29 Sep 2004 04:45:25 +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 68560-04 for ; Wed, 29 Sep 2004 03:45:14 +0000 (GMT) Received: from rambo.iniquinet.com (rambo.iniquinet.com [69.39.89.10]) by svr1.postgresql.org (Postfix) with ESMTP id 81B0A32A161 for ; Wed, 29 Sep 2004 04:44:42 +0100 (BST) Received: (qmail 17553 invoked by uid 1010); 28 Sep 2004 23:44:29 -0400 Received: from Robert_Creager@LogicalChaos.org by rambo.iniquinet.com by uid 1002 with qmail-scanner-1.20 (clamscan: 0.70. spamassassin: 2.64. Clear:RC:0(216.150.208.13):SA:0(-4.6/6.0):. Processed in 1.750459 secs); 29 Sep 2004 03:44:29 -0000 Received: from static-13.mm.den.viawest.net (HELO thunder.mshome.net) (perl?test@logicalchaos.org@216.150.208.13) by rambo.iniquinet.com with AES256-SHA encrypted SMTP; 28 Sep 2004 23:44:28 -0400 Received: from localhost.localdomain (thunder.mshome.net [192.168.0.250]) by thunder.mshome.net (Postfix) with ESMTP id 36F0BA8237; Tue, 28 Sep 2004 21:44:26 -0600 (MDT) Received: from logicalchaos.org (thunder.mshome.net [192.168.0.250]) by thunder.mshome.net (Postfix) with SMTP id E869DA8237; Tue, 28 Sep 2004 21:44:24 -0600 (MDT) Date: Tue, 28 Sep 2004 21:44:24 -0600 From: Robert Creager To: Tom Lane Cc: PGPerformance Subject: Re: This query is still running after 10 hours... Message-Id: <20040928214424.2583b925@thunder.mshome.net> In-Reply-To: <14842.1096383863@sss.pgh.pa.us> References: <20040928081957.7a317a95@thunder.mshome.net> <14842.1096383863@sss.pgh.pa.us> Organization: Starlight Vision, LLC. X-Mailer: Sylpheed version 0.9.10claws (GTK+ 1.2.10; i586-mandrake-linux-gnu) Mime-Version: 1.0 Content-Type: multipart/signed; protocol="application/pgp-signature"; micalg="pgp-sha1"; boundary="Signature=_Tue__28_Sep_2004_21_44_24_-0600_z6MwwaicMC+jj6K2" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200409/319 X-Sequence-Number: 8447 --Signature=_Tue__28_Sep_2004_21_44_24_-0600_z6MwwaicMC+jj6K2 Content-Type: multipart/mixed; boundary="Multipart=_Tue__28_Sep_2004_21_44_24_-0600_T=3Spy18auU7fXX_" --Multipart=_Tue__28_Sep_2004_21_44_24_-0600_T=3Spy18auU7fXX_ Content-Type: text/plain; charset=US-ASCII Content-Disposition: inline Content-Transfer-Encoding: 7bit When grilled further on (Tue, 28 Sep 2004 11:04:23 -0400), Tom Lane confessed: > Robert Creager writes: > > Normally, this query takes from 5 minutes to 2 hours to run. On this > > update, it's been running for more than 10 hours. > > > ... > > -> Nested Loop (cost=250.69..129094.19 rows=77211 width=59) > > -> Hash Join (cost=250.69..307.34 rows=67 width=12) > > Hash Cond: ("outer".pair_id = "inner".pair_id) > > ... > > It chose a nested loop here because it was only expecting 67 rows out of > the next-lower join, and so it thought it would only need 67 repetitions > of the index probe into obs_v_file_id_index. I'm suspicious that that > estimate was way low and so the nestloop is taking forever. You might > try "SET enable_nestloop = off" as a crude way of avoiding that trap. I tried your suggestion. Did generate a different plan (below), but the estimation is blown as it still used a nested loop. The query is currently running(42 minutes so far). For the query in question, there are 151 different pair_id's in the pairs table, which equates to 302 entries in the files table (part of the query), which moves on to 533592 entries in the obs_v table and 533699 entries in the obs_i table. The groups table has 76 total entries, files 9028, zero_pair 2532, color_groups 147. Only the obs_v and obs_i tables have data of any significant quantities with 10M rows apiece. The trigger hitting the catalog table (875499 entries) is searching for single entries to match (one fire per obs_v/obs_i update) on an index (took 54ms on the first query of a random id just now). There is no significant disk activity (read 0), one CPU is pegged, and that process is consuming 218M Resident memory, 168M Shared (10% available memory total). All reasonable, except for the fact it doesn't come back... Hash Join (cost=100267870.17..100751247.13 rows=1578889 width=63) Hash Cond: ("outer".star_id = "inner".star_id) -> Seq Scan on obs_i i (cost=0.00..213658.19 rows=10391319 width=8) -> Hash (cost=100266886.39..100266886.39 rows=77113 width=59) -> Hash Join (cost=100000307.51..100266886.39 rows=77113 width=59) Hash Cond: ("outer".file_id = "inner".file_id) -> Seq Scan on obs_v (cost=0.00..213854.50 rows=10390650 width=5 1) -> Hash (cost=100000307.34..100000307.34 rows=67 width=12) -> Hash Join (cost=100000250.69..100000307.34 rows=67 width=12) Hash Cond: ("outer".pair_id = "inner".pair_id) -> Seq Scan on zero_pair zp (cost=0.00..43.32 rows=2532 width=8) -> Hash (cost=100000250.40..100000250.40 rows=118 width=12) -> Hash Join (cost=100000004.80..100000250.40 rows=118 width=12) Hash Cond: ("outer".night_id = "inner".night_id) -> Seq Scan on files f (cost=0.00..199.28 rows=9028 width=12) -> Hash (cost=100000004.80..100000004.80rows=1 width=8) -> Nested Loop (cost=100000000.00..100000004.80 rows=1 width=8) -> Seq Scan on color_groups cg (cost=0.00..2.84 rows=1 width=8) Filter: (175 = group_id) -> Seq Scan on groups g (cost=0.00..1.95 rows=1 width=8) Filter: (group_id = 175) -- 20:48:23 up 5 days, 23:14, 2 users, load average: 2.56, 2.91, 2.78 Linux 2.6.5-02 #8 SMP Mon Jul 12 21:34:44 MDT 2004 --Multipart=_Tue__28_Sep_2004_21_44_24_-0600_T=3Spy18auU7fXX_ Content-Type: application/octet-stream; name="color.explain" Content-Disposition: attachment; filename="color.explain" Content-Transfer-Encoding: base64 VVBEQVRFIG9ic192ClNFVCBtYWcgPSBvYnNfdi5pbWFnICsgenAuemVyb192 ICsgY2cuY29sb3JfdiAqIChvYnNfdi5pbWFnIC0gaS5pbWFnKSwKICAgIHVz ZSA9IHRydWUKRlJPTSBjb2xvcl9ncm91cHMgQVMgY2csIHplcm9fcGFpciBB UyB6cCwgb2JzX2kgQVMgaSwgZmlsZXMgQVMgZiwgZ3JvdXBzIEFTIGcKV0hF UkUgIG9ic192LnN0YXJfaWQgPSBpLnN0YXJfaWQKICAgQU5EIG9ic192LmZp bGVfaWQgPSBmLmZpbGVfaWQKICAgQU5EIGNnLmdyb3VwX2lkID0gZy5ncm91 cF9pZAogICBBTkQgZy5uaWdodF9pZCA9IGYubmlnaHRfaWQKICAgQU5EIGcu Z3JvdXBfaWQgPSAkZ3JvdXBfaWQKICAgQU5EIHpwLnBhaXJfaWQgPSBmLnBh aXJfaWQKCkhhc2ggSm9pbiAgKGNvc3Q9MTMwMDc5LjIyLi42Mzk2NjMuOTQg cm93cz0xNTkwMjA0IHdpZHRoPTYzKQogIEhhc2ggQ29uZDogKCJvdXRlciIu c3Rhcl9pZCA9ICJpbm5lciIuc3Rhcl9pZCkKICAtPiAgU2VxIFNjYW4gb24g b2JzX2kgaSAgKGNvc3Q9MC4wMC4uMjEzNjU4LjE5IHJvd3M9MTAzOTEzMTkg d2lkdGg9OCkKICAtPiAgSGFzaCAgKGNvc3Q9MTI5MDk0LjE5Li4xMjkwOTQu MTkgcm93cz03NzIxMSB3aWR0aD01OSkKICAgICAgICAtPiAgTmVzdGVkIExv b3AgIChjb3N0PTI1MC42OS4uMTI5MDk0LjE5IHJvd3M9NzcyMTEgd2lkdGg9 NTkpCiAgICAgICAgICAgICAgLT4gIEhhc2ggSm9pbiAgKGNvc3Q9MjUwLjY5 Li4zMDcuMzQgcm93cz02NyB3aWR0aD0xMikKICAgICAgICAgICAgICAgICAg ICBIYXNoIENvbmQ6ICgib3V0ZXIiLnBhaXJfaWQgPSAiaW5uZXIiLnBhaXJf aWQpCiAgICAgICAgICAgICAgICAgICAgLT4gIFNlcSBTY2FuIG9uIHplcm9f cGFpciB6cCAgKGNvc3Q9MC4wMC4uNDMuMzIgcm93cz0yNTMyIHdpZHRoPTgp CiAgICAgICAgICAgICAgICAgICAgLT4gIEhhc2ggIChjb3N0PTI1MC40MC4u MjUwLjQwIHJvd3M9MTE4IHdpZHRoPTEyKQogICAgICAgICAgICAgICAgICAg ICAgICAgIC0+ICBIYXNoIEpvaW4gIChjb3N0PTQuODAuLjI1MC40MCByb3dz PTExOCB3aWR0aD0xMikKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICBIYXNoIENvbmQ6ICgib3V0ZXIiLm5pZ2h0X2lkID0gImlubmVyIi5uaWdo dF9pZCkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgU2Vx IFNjYW4gb24gZmlsZXMgZiAgKGNvc3Q9MC4wMC4uMTk5LjI4IHJvd3M9OTAy OCB3aWR0aD0xMikKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAt PiAgSGFzaCAgKGNvc3Q9NC44MC4uNC44MCByb3dzPTEgd2lkdGg9OCkKICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAtPiAgTmVzdGVk IExvb3AgIChjb3N0PTAuMDAuLjQuODAgcm93cz0xIHdpZHRoPTgpCiAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIFNl cSBTY2FuIG9uIGNvbG9yX2dyb3VwcyBjZyAgKGNvc3Q9MC4wMC4uMi44NCBy b3dzPTEgd2lkdGg9OCkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICBGaWx0ZXI6ICgxNzEgPSBncm91cF9pZCkK ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAt PiAgU2VxIFNjYW4gb24gZ3JvdXBzIGcgIChjb3N0PTAuMDAuLjEuOTUgcm93 cz0xIHdpZHRoPTgpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgRmlsdGVyOiAoZ3JvdXBfaWQgPSAxNzEpCiAg ICAgICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgb2JzX3ZfZmlsZV9p ZF9pbmRleCBvbiBvYnNfdiAgKGNvc3Q9MC4wMC4uMTg5My4yMyByb3dzPTIz MTcgd2lkdGg9NTEpCiAgICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDog KG9ic192LmZpbGVfaWQgPSAib3V0ZXIiLmZpbGVfaWQpCgpIYXNoIEpvaW4g IChjb3N0PTEwMDI2Nzg3MC4xNy4uMTAwNzUxMjQ3LjEzIHJvd3M9MTU3ODg4 OSB3aWR0aD02MykKICBIYXNoIENvbmQ6ICgib3V0ZXIiLnN0YXJfaWQgPSAi aW5uZXIiLnN0YXJfaWQpCiAgLT4gIFNlcSBTY2FuIG9uIG9ic19pIGkgIChj b3N0PTAuMDAuLjIxMzY1OC4xOSByb3dzPTEwMzkxMzE5IHdpZHRoPTgpCiAg LT4gIEhhc2ggIChjb3N0PTEwMDI2Njg4Ni4zOS4uMTAwMjY2ODg2LjM5IHJv d3M9NzcxMTMgd2lkdGg9NTkpCiAgICAgICAgLT4gIEhhc2ggSm9pbiAgKGNv c3Q9MTAwMDAwMzA3LjUxLi4xMDAyNjY4ODYuMzkgcm93cz03NzExMyB3aWR0 aD01OSkKICAgICAgICAgICAgICBIYXNoIENvbmQ6ICgib3V0ZXIiLmZpbGVf aWQgPSAiaW5uZXIiLmZpbGVfaWQpCiAgICAgICAgICAgICAgLT4gIFNlcSBT Y2FuIG9uIG9ic192ICAoY29zdD0wLjAwLi4yMTM4NTQuNTAgcm93cz0xMDM5 MDY1MCB3aWR0aD01IDEpCiAgICAgICAgICAgICAgLT4gIEhhc2ggIChjb3N0 PTEwMDAwMDMwNy4zNC4uMTAwMDAwMzA3LjM0IHJvd3M9Njcgd2lkdGg9MTIp CiAgICAgICAgICAgICAgICAgICAgLT4gIEhhc2ggSm9pbiAgKGNvc3Q9MTAw MDAwMjUwLjY5Li4xMDAwMDAzMDcuMzQgcm93cz02NyB3aWR0aD0xMikKICAg ICAgICAgICAgICAgICAgICAgICAgICBIYXNoIENvbmQ6ICgib3V0ZXIiLnBh aXJfaWQgPSAiaW5uZXIiLnBhaXJfaWQpCiAgICAgICAgICAgICAgICAgICAg ICAgICAgLT4gIFNlcSBTY2FuIG9uIHplcm9fcGFpciB6cCAgKGNvc3Q9MC4w MC4uNDMuMzIgcm93cz0yNTMyIHdpZHRoPTgpCiAgICAgICAgICAgICAgICAg ICAgICAgICAgLT4gIEhhc2ggIChjb3N0PTEwMDAwMDI1MC40MC4uMTAwMDAw MjUwLjQwIHJvd3M9MTE4IHdpZHRoPTEyKQogICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgIC0+ICBIYXNoIEpvaW4gIChjb3N0PTEwMDAwMDAwNC44 MC4uMTAwMDAwMjUwLjQwIHJvd3M9MTE4IHdpZHRoPTEyKQogICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgIEhhc2ggQ29uZDogKCJvdXRl ciIubmlnaHRfaWQgPSAiaW5uZXIiLm5pZ2h0X2lkKQogICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBTZXEgU2NhbiBvbiBmaWxl cyBmICAoY29zdD0wLjAwLi4xOTkuMjggcm93cz05MDI4IHdpZHRoPTEyKQog ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgIC0+ICBIYXNo ICAoY29zdD0xMDAwMDAwMDQuODAuLjEwMDAwMDAwNC44MHJvd3M9MSB3aWR0 aD04KQogICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgIC0+ICBOZXN0ZWQgTG9vcCAgKGNvc3Q9MTAwMDAwMDAwLjAwLi4xMDAw MDAwMDQuODAgcm93cz0xIHdpZHRoPTgpCiAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgLT4gIFNlcSBTY2FuIG9u IGNvbG9yX2dyb3VwcyBjZyAgKGNvc3Q9MC4wMC4uMi44NCByb3dzPTEgd2lk dGg9OCkKICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICBGaWx0ZXI6ICgxNzUgPSBncm91cF9pZCkKICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAtPiAgU2VxIFNjYW4gb24gZ3JvdXBzIGcgIChjb3N0PTAuMDAuLjEuOTUg cm93cz0xIHdpZHRoPTgpCiAgICAgICAgICAgICAgICAgICAgICAgICAgICAg ICAgICAgICAgICAgICAgICAgICAgICAgICAgRmlsdGVyOiAoZ3JvdXBfaWQg PSAxNzUpCgo= --Multipart=_Tue__28_Sep_2004_21_44_24_-0600_T=3Spy18auU7fXX_-- --Signature=_Tue__28_Sep_2004_21_44_24_-0600_z6MwwaicMC+jj6K2 Content-Type: application/pgp-signature -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) iEYEARECAAYFAkFaL5gACgkQLQ/DKuwDYzkWcwCdHKHLxmMydV8CvQAT2DJBBCDY MiQAoJh2KtRrDNvSdxmJgzCQ/8v35Vhn =YUs9 -----END PGP SIGNATURE----- --Signature=_Tue__28_Sep_2004_21_44_24_-0600_z6MwwaicMC+jj6K2--