Re: Fw: Infinite CPU loop due to field ::type casting, Take II :-)

Steven Rosenstein <srosenst(at)us(dot)ibm(dot)com> 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