X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 4636E11F60B6 for ; Fri, 28 Apr 2006 06:00:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 21053-07 for ; Fri, 28 Apr 2006 06:00:44 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from floppy.pyrenet.fr (news.pyrenet.fr [194.116.145.2]) by postgresql.org (Postfix) with ESMTP id 4CE2D11F609E for ; Fri, 28 Apr 2006 06:00:43 -0300 (ADT) Received: by floppy.pyrenet.fr (Postfix, from userid 106) id 0351031478; Fri, 28 Apr 2006 11:00:43 +0200 (MET DST) From: "Andrus" X-Newsgroups: pgsql.performance Subject: Re: CPU usage goes to 100%, query seems to ran forever Date: Fri, 28 Apr 2006 12:00:35 +0300 Organization: Hub.Org Networking Services Lines: 48 Message-ID: References: <14690.1146164953@sss.pgh.pa.us> X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869 X-RFC2646: Format=Flowed; Original To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/667 X-Sequence-Number: 18751 > You have ANALYZEd all these tables recently, I hope? The planner > certainly doesn't think this query will take very long. I have autovacuum running so I expect it takes care of ANALYZE, isn't it ? I ran also analyze command before running explain analyze. > To find out what's wrong, you're going to have to be patient enough to > let an EXPLAIN ANALYZE run to completion. Plain EXPLAIN won't tell. Here it is running in my local computer. I'm expecting run time no more 1 second "Nested Loop Left Join (cost=0.00..1829.95 rows=1 width=24) (actual time=492064.990..492064.990 rows=0 loops=1)" " Filter: ((("inner".arttyyp <> 'Teenus'::bpchar) AND ("inner".arttyyp <> 'Komplekt'::bpchar)) OR ("inner".arttyyp IS NULL))" " -> Nested Loop (cost=0.00..1825.01 rows=1 width=43) (actual time=492064.983..492064.983 rows=0 loops=1)" " -> Nested Loop (cost=0.00..1819.04 rows=1 width=24) (actual time=492064.978..492064.978 rows=0 loops=1)" " Join Filter: (("outer".dokumnr = "inner".dokumnr) AND ((("position"('VGYKITDNHMEBARCFJ'::text, ("outer".doktyyp)::text) <> 0) AND (CASE WHEN ((NOT ("outer".objrealt)::boolean) OR ("outer".doktyyp = 'I'::bpchar)) THEN "outer".yksus ELSE "inner (..)" " -> Seq Scan on dok (cost=0.00..787.80 rows=1 width=39) (actual time=0.152..878.198 rows=7670 loops=1)" " Filter: ((kuupaev >= '2006-04-08'::date) AND (kuupaev <= '2006-04-27'::date) AND (NOT (eimuuda)::boolean) AND ((laonr)::double precision = 1::double precision) AND ("position"('OSIDVGYKIF'::text, (doktyyp)::text) <> 0) AND (((kuupaev):: (..)" " -> Seq Scan on rid (cost=0.00..1019.42 rows=315 width=51) (actual time=22.003..62.216 rows=839 loops=7670)" " Filter: ((toode = '1EEKPANT'::bpchar) AND (length(btrim((toode)::text)) > 2) AND (toode IS NOT NULL))" " -> Index Scan using toode_pkey on toode (cost=0.00..5.96 rows=1 width=43) (never executed)" " Index Cond: ('1EEKPANT'::bpchar = toode)" " -> Index Scan using artliik_pkey on artliik (cost=0.00..4.92 rows=1 width=31) (never executed)" " Index Cond: (("outer".grupp = artliik.grupp) AND ("outer".liik = artliik.liik))" "Total runtime: 492065.840 ms" Andrus.