Re: Stuck on SQL - Any Takers?

"Tim Perdue" <perdue@raccoon.com> 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