Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by hub.org (8.9.2/8.9.1) with ESMTP id KAA76869 for ; Fri, 19 Mar 1999 10:22:41 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA25578 for ; Fri, 19 Mar 1999 10:22:00 -0500 (EST) To: pgsql-sql@hub.org Subject: Re: Stuck on SQL - Any Takers? In-reply-to: Your message of Thu, 18 Mar 1999 23:00:34 -0500 (EST) <199903190400.XAA16650@hub.org> Date: Fri, 19 Mar 1999 10:22:00 -0500 Message-ID: <25576.921856920@sss.pgh.pa.us> From: Tom Lane "Tim Perdue" writes: > I have a table where each record has nine player fields, like this: > ... > I'm trying to build a new table that pulls the related player's name and > points from another table, but it isn't working. I'm getting this error: > ERROR: create: repeated attribute "fld_player_last" > [ from a SELECT ... INTO ] You didn't say whether you figured that out, but you are going to have to add AS clauses to the SELECT so that each column has a distinct title. In a plain SELECT the system is not picky about duplicate column titles, but when it's trying to create an output table it is. > I played with the query a little bit. In a nutshell, the performance is > nothing short of horrible. 27:01 CPU time and no results - not even one > record inserted into the new table. If you're on 6.4, you're probably running into some of the optimizer bugs that Bruce fixed for 6.5. These bugs cause planning of queries that join more than about half a dozen tables to take an unreasonable amount of time. (If EXPLAINing the query takes a long time too, that's definitely what you are hitting.) You can work around the bugs by setting GEQO to a smaller value, like about 6. What plan does EXPLAIN report for this query, anyway? regards, tom lane