pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steven Rosenstein <srosenst@us.ibm.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Fw: Infinite CPU loop due to field ::type casting, Take II :-)
Date: Wed, 22 Sep 2004 18:03:45 -0400
Message-ID: <19167.1095890625@sss.pgh.pa.us> (raw)
In-Reply-To: <OF31A95FE9.5950C366-ON85256F17.0070339C-85256F17.0070E90F@us.ibm.com>
References: <OF31A95FE9.5950C366-ON85256F17.0070339C-85256F17.0070E90F@us.ibm.com>

Steven Rosenstein <srosenst@us.ibm.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



view thread (3+ messages)

Message-ID: <19167.1095890625@sss.pgh.pa.us>
Permalink:  ../19167.1095890625@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/19167.1095890625@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: tgl@sss.pgh.pa.us, srosenst@us.ibm.com
  Subject: Re: Fw: Infinite CPU loop due to field ::type casting, Take II :-)
  In-Reply-To: <19167.1095890625@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox