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 25972329CDB for ; Wed, 22 Sep 2004 23:03:53 +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 52762-05 for ; Wed, 22 Sep 2004 22:03:44 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B1D97329E69 for ; Wed, 22 Sep 2004 23:03:45 +0100 (BST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i8MM3j64019168; Wed, 22 Sep 2004 18:03:45 -0400 (EDT) To: Steven Rosenstein Cc: pgsql-performance@postgresql.org Subject: Re: Fw: Infinite CPU loop due to field ::type casting, Take II :-) In-reply-to: References: Comments: In-reply-to Steven Rosenstein message dated "Wed, 22 Sep 2004 16:33:16 -0400" Date: Wed, 22 Sep 2004 18:03:45 -0400 Message-ID: <19167.1095890625@sss.pgh.pa.us> From: Tom Lane 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/228 X-Sequence-Number: 8356 Steven Rosenstein writes: > Environment: Red Hat Enterprise Linux 3 Workstation, PostgreSQL V7.3.6 > vsa=# explain > SELECT dev.name, dev.vss_site_id, tbl.log_type, tbl.severity, tbl.count > FROM vsa.tbl_device AS dev > LEFT OUTER JOIN > (SELECT stbl.device_id, stbl.log_type, stbl.severity, count(*) > FROM vsa.dtbl_logged_event_20040922 AS stbl > WHERE stbl.log_type IN (2, 3, 4, 5) GROUP BY stbl.device_id, > stbl.log_type, stbl.severity) AS tbl > ON (dev.id=tbl.device_id::int) > ORDER BY dev.name; > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------------------ > Nested Loop (cost=0.00..45848850.65 rows=27100 width=79) > Join Filter: ("outer".id = ("inner".device_id)::integer) > -> Index Scan using idx_d_name on tbl_device dev (cost=0.00..1490.19 rows=1275 width=26) > -> Subquery Scan tbl (cost=0.00..35552.21 rows=27100 width=26) > -> Aggregate (cost=0.00..35552.21 rows=27100 width=26) > -> Group (cost=0.00..34874.70 rows=271005 width=26) > -> Index Scan using idx_le_id_type_severity_evtcode_20040922 on dtbl_logged_event_20040922 stbl (cost=0.00..32842.16 > rows=271005 width=26) > Filter: ((log_type = 2) OR (log_type = 3) OR (log_type = 4) OR (log_type = 5)) > (8 rows) > Time: 1.62 ms > Notice that the query plan changes completely when I cast device_id as int. > What is worse (and why I'm writing) is that when I run the second query, it > goes into an infinite CPU loop. "Bad plan" and "infinite loop" are two very different things. In 7.3 you'd be better off without the cast, as you just found out. The 7.3 backend can only handle merge or hash joins that use a join clause of the form "variable = variable" --- anything more complicated falls back to a nested loop join. It does handle mergejoins between unlike data types, though, so you were doing okay with the undecorated query. 7.4 is smarter; dunno if you want to upgrade at this point. regards, tom lane