pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: pgsql-sql@hub.org
Subject: Re: Stuck on SQL - Any Takers?
Date: Fri, 19 Mar 1999 10:22:00 -0500
Message-ID: <25576.921856920@sss.pgh.pa.us> (raw)
In-Reply-To: <199903190400.XAA16650@hub.org>

"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



view thread (7+ messages)  latest in thread

Message-ID: <25576.921856920@sss.pgh.pa.us>
Permalink:  ../25576.921856920@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/25576.921856920@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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, pgsql-sql@hub.org
  Subject: Re: Stuck on SQL - Any Takers?
  In-Reply-To: <25576.921856920@sss.pgh.pa.us>
  References: <199903190400.XAA16650@hub.org> <25576.921856920@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