pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedStuck on SQL - Any Takers?
7+ messages / 5 participants
[nested] [flat]
* Stuck on SQL - Any Takers?
@ 1999-03-18 23:44 Tim Perdue <perdue@raccoon.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tim Perdue @ 1999-03-18 23:44 UTC (permalink / raw)
To: pgsql-sql@hub.org
I have a table where each record has nine player fields, like this:
tbl_teams (
fld_team_no int primary key,
fld_cust_no int,
fld_athlete1 int,
fld_athlete2 int,
fld_athlete3 int,
fld_athlete4 int,
fld_athlete5 int,
fld_athlete6 int,
fld_athlete7 int,
fld_athlete8 int,
fld_athlete9 int
);
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"
Is there a better way to write this query?
SELECT
tbl_cust.fld_cust_name,
tbl_zip_codes.fld_zip_code,
tbl_zip_codes.fld_city_name,
tbl_zip_codes.fld_state_short,
tbl_zip_codes.fld_area_code,
tbl_teams.fld_team_no,
tbl_athlete_stats_1.fld_player_last,
tbl_athlete_stats_1.fld_player_first,
tbl_athlete_stats_1.fld_total_points_ytd,
tbl_athlete_stats_2.fld_player_last,
tbl_athlete_stats_2.fld_player_first,
tbl_athlete_stats_2.fld_total_points_ytd,
tbl_athlete_stats_3.fld_player_last,
tbl_athlete_stats_3.fld_player_first,
tbl_athlete_stats_3.fld_total_points_ytd,
tbl_athlete_stats_4.fld_player_last,
tbl_athlete_stats_4.fld_player_first,
tbl_athlete_stats_4.fld_total_points_ytd,
tbl_athlete_stats_5.fld_player_last,
tbl_athlete_stats_5.fld_player_first,
tbl_athlete_stats_5.fld_total_points_ytd,
tbl_athlete_stats_6.fld_player_last,
tbl_athlete_stats_6.fld_player_first,
tbl_athlete_stats_6.fld_total_points_ytd,
tbl_athlete_stats_7.fld_player_last,
tbl_athlete_stats_7.fld_player_first,
tbl_athlete_stats_7.fld_total_points_ytd,
tbl_athlete_stats_8.fld_player_last,
tbl_athlete_stats_8.fld_player_first,
tbl_athlete_stats_8.fld_total_points_ytd,
tbl_athlete_stats_9.fld_player_last,
tbl_athlete_stats_9.fld_player_first,
tbl_athlete_stats_9.fld_total_points_ytd,
(tbl_athlete_stats_1.fld_total_points_ytd+
tbl_athlete_stats_2.fld_total_points_ytd+
tbl_athlete_stats_3.fld_total_points_ytd+
tbl_athlete_stats_4.fld_total_points_ytd+
tbl_athlete_stats_5.fld_total_points_ytd+
tbl_athlete_stats_6.fld_total_points_ytd+
tbl_athlete_stats_7.fld_total_points_ytd+
tbl_athlete_stats_8.fld_total_points_ytd+
tbl_athlete_stats_9.fld_total_points_ytd)
AS fld_team_score
INTO tbl_ranked_teams
FROM
tbl_athlete_stats tbl_athlete_stats_1,
tbl_athlete_stats tbl_athlete_stats_2,
tbl_athlete_stats tbl_athlete_stats_3,
tbl_athlete_stats tbl_athlete_stats_4,
tbl_athlete_stats tbl_athlete_stats_5,
tbl_athlete_stats tbl_athlete_stats_6,
tbl_athlete_stats tbl_athlete_stats_7,
tbl_athlete_stats tbl_athlete_stats_8,
tbl_athlete_stats tbl_athlete_stats_9
WHERE
tbl_athlete_stats_1.fld_player_number = tbl_teams.fld_athlete1 and
tbl_athlete_stats_2.fld_player_number = tbl_teams.fld_athlete2 and
tbl_athlete_stats_3.fld_player_number = tbl_teams.fld_athlete3 and
tbl_athlete_stats_4.fld_player_number = tbl_teams.fld_athlete4 and
tbl_athlete_stats_5.fld_player_number = tbl_teams.fld_athlete5 and
tbl_athlete_stats_6.fld_player_number = tbl_teams.fld_athlete6 and
tbl_athlete_stats_7.fld_player_number = tbl_teams.fld_athlete7 and
tbl_athlete_stats_8.fld_player_number = tbl_teams.fld_athlete8 and
tbl_athlete_stats_9.fld_player_number = tbl_teams.fld_athlete9 and
tbl_teams.fld_cust_no = tbl_cust.fld_cust_no and
tbl_cust.fld_zip = tbl_zip_codes.fld_zip_code
ORDER BY fld_team_score DESC;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Stuck on SQL - Any Takers?
@ 1999-03-19 00:58 Matthew Diesch <matthew@manufact.co.nz>
0 siblings, 0 replies; 7+ messages in thread
From: Matthew Diesch @ 1999-03-19 00:58 UTC (permalink / raw)
To: pgsql-sql@hub.org
..snip snip
>
>ORDER BY fld_team_score DESC;
>
It's not specifically about youre error but this order by is surely
redundant and just slows down the query.
Matthew
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Stuck on SQL - Any Takers?
@ 1999-03-19 02:32 Tim Perdue <perdue@raccoon.com>
1999-03-19 03:16 ` Re: [SQL] Stuck on SQL - Any Takers? twm139@its.to
0 siblings, 1 reply; 7+ messages in thread
From: Tim Perdue @ 1999-03-19 02:32 UTC (permalink / raw)
To: pgsql-sql@hub.org
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.
The same query completes in 5 seconds on MS Access.
There must be a way to optimize my query so it runs in some sensible way on
postgres??
Tim
insert into tbl_ranked_teams2
SELECT
tbl_teams.fld_team_no,
tbl_athlete_stats_1.fld_total_points_ytd+
tbl_athlete_stats_2.fld_total_points_ytd+
tbl_athlete_stats_3.fld_total_points_ytd+
tbl_athlete_stats_4.fld_total_points_ytd+
tbl_athlete_stats_5.fld_total_points_ytd+
tbl_athlete_stats_6.fld_total_points_ytd+
tbl_athlete_stats_7.fld_total_points_ytd+
tbl_athlete_stats_8.fld_total_points_ytd+
tbl_athlete_stats_9.fld_total_points_ytd
FROM
tbl_athlete_stats tbl_athlete_stats_1,
tbl_athlete_stats tbl_athlete_stats_2,
tbl_athlete_stats tbl_athlete_stats_3,
tbl_athlete_stats tbl_athlete_stats_4,
tbl_athlete_stats tbl_athlete_stats_5,
tbl_athlete_stats tbl_athlete_stats_6,
tbl_athlete_stats tbl_athlete_stats_7,
tbl_athlete_stats tbl_athlete_stats_8,
tbl_athlete_stats tbl_athlete_stats_9
WHERE
tbl_athlete_stats_1.fld_player_number = tbl_teams.fld_athlete1 and
tbl_athlete_stats_2.fld_player_number = tbl_teams.fld_athlete2 and
tbl_athlete_stats_3.fld_player_number = tbl_teams.fld_athlete3 and
tbl_athlete_stats_4.fld_player_number = tbl_teams.fld_athlete4 and
tbl_athlete_stats_5.fld_player_number = tbl_teams.fld_athlete5 and
tbl_athlete_stats_6.fld_player_number = tbl_teams.fld_athlete6 and
tbl_athlete_stats_7.fld_player_number = tbl_teams.fld_athlete7 and
tbl_athlete_stats_8.fld_player_number = tbl_teams.fld_athlete8 and
tbl_athlete_stats_9.fld_player_number = tbl_teams.fld_athlete9;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Stuck on SQL - Any Takers?
1999-03-19 02:32 Re: [SQL] Stuck on SQL - Any Takers? Tim Perdue <perdue@raccoon.com>
@ 1999-03-19 03:16 ` twm139@its.to
0 siblings, 0 replies; 7+ messages in thread
From: twm139@its.to @ 1999-03-19 03:16 UTC (permalink / raw)
To: Tim Perdue <perdue@raccoon.com>; +Cc: pgsql-sql@hub.org
Could you give me some more information. What does your data model look like?
The table you describe below looks like it could be seperated into two tables.
Also is the select going into a permanent table that will be maintained
seperately or is it more transient, a view may also be suitable.
Terrence
On 19-Mar-99 Tim Perdue wrote:
> 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.
>
> The same query completes in 5 seconds on MS Access.
>
> There must be a way to optimize my query so it runs in some sensible way on
> postgres??
>
> Tim
>
>
> insert into tbl_ranked_teams2
> SELECT
> tbl_teams.fld_team_no,
> tbl_athlete_stats_1.fld_total_points_ytd+
> tbl_athlete_stats_2.fld_total_points_ytd+
> tbl_athlete_stats_3.fld_total_points_ytd+
> tbl_athlete_stats_4.fld_total_points_ytd+
> tbl_athlete_stats_5.fld_total_points_ytd+
> tbl_athlete_stats_6.fld_total_points_ytd+
> tbl_athlete_stats_7.fld_total_points_ytd+
> tbl_athlete_stats_8.fld_total_points_ytd+
> tbl_athlete_stats_9.fld_total_points_ytd
> FROM
> tbl_athlete_stats tbl_athlete_stats_1,
> tbl_athlete_stats tbl_athlete_stats_2,
> tbl_athlete_stats tbl_athlete_stats_3,
> tbl_athlete_stats tbl_athlete_stats_4,
> tbl_athlete_stats tbl_athlete_stats_5,
> tbl_athlete_stats tbl_athlete_stats_6,
> tbl_athlete_stats tbl_athlete_stats_7,
> tbl_athlete_stats tbl_athlete_stats_8,
> tbl_athlete_stats tbl_athlete_stats_9
> WHERE
> tbl_athlete_stats_1.fld_player_number = tbl_teams.fld_athlete1 and
> tbl_athlete_stats_2.fld_player_number = tbl_teams.fld_athlete2 and
> tbl_athlete_stats_3.fld_player_number = tbl_teams.fld_athlete3 and
> tbl_athlete_stats_4.fld_player_number = tbl_teams.fld_athlete4 and
> tbl_athlete_stats_5.fld_player_number = tbl_teams.fld_athlete5 and
> tbl_athlete_stats_6.fld_player_number = tbl_teams.fld_athlete6 and
> tbl_athlete_stats_7.fld_player_number = tbl_teams.fld_athlete7 and
> tbl_athlete_stats_8.fld_player_number = tbl_teams.fld_athlete8 and
> tbl_athlete_stats_9.fld_player_number = tbl_teams.fld_athlete9;
>
----------------------------------
E-Mail: twm139@its.to
Date: 18-Mar-99
Time: 19:59:20
Lottery: A tax on people who are bad at math.
----------------------------------
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Stuck on SQL - Any Takers?
@ 1999-03-19 04:13 Tim Perdue <perdue@raccoon.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tim Perdue @ 1999-03-19 04:13 UTC (permalink / raw)
To: twm139@its.to; pgsql-sql@hub.org
Here are the tables. The first table is not really designed very well. Each
record has nine fields that has a athlete_no in it. That athlete_no relates
to tbl_athlete_stats.
For each record in tbl_teams, I want to pull the corresponding scores out of
tbl_athlete_stats and create a new table, tbl_ranked_teams2.
My query (at the bottom) does not error out, but it never inserts any
records or completes.
tbl_teams (
fld_team_no int primary key,
fld_cust_no int,
fld_athlete1 int, --> fld_player_number in tbl_athlete_stats
fld_athlete2 int, --> fld_player_number in tbl_athlete_stats
fld_athlete3 int, --> fld_player_number in tbl_athlete_stats
fld_athlete4 int, --> fld_player_number in tbl_athlete_stats
fld_athlete5 int, --> fld_player_number in tbl_athlete_stats
fld_athlete6 int, --> fld_player_number in tbl_athlete_stats
fld_athlete7 int, --> fld_player_number in tbl_athlete_stats
fld_athlete8 int, --> fld_player_number in tbl_athlete_stats
fld_athlete9 int --> fld_player_number in tbl_athlete_stats
);
tbl_athlete_stats (
fld_player_number int primary key,
fld_player_last text,
fld_player_first text,
fld_player_id int,
fld_team text,
fld_position text,
fld_batting_ave float4,
fld_singles int,
fld_doubles int,
fld_triples int,
fld_home_runs int,
fld_rbis int,
fld_walks int,
fld_teals int,
fld_runs int,
fld_total_points_wk int,
fld_total_points_ytd int
);
create table tbl_ranked_teams2 (
fld_team_no int,
fld_athlete1_ytd int,
fld_athlete2_ytd int,
fld_athlete3_ytd int,
fld_athlete4_ytd int,
fld_athlete5_ytd int,
fld_athlete6_ytd int,
fld_athlete7_ytd int,
fld_athlete8_ytd int,
fld_athlete9_ytd int
);
insert into tbl_ranked_teams2
SELECT
tbl_teams.fld_team_no,
tbl_athlete_stats_1.fld_total_points_ytd,
tbl_athlete_stats_2.fld_total_points_ytd,
tbl_athlete_stats_3.fld_total_points_ytd,
tbl_athlete_stats_4.fld_total_points_ytd,
tbl_athlete_stats_5.fld_total_points_ytd,
tbl_athlete_stats_6.fld_total_points_ytd,
tbl_athlete_stats_7.fld_total_points_ytd,
tbl_athlete_stats_8.fld_total_points_ytd,
tbl_athlete_stats_9.fld_total_points_ytd
FROM
tbl_athlete_stats tbl_athlete_stats_1,
tbl_athlete_stats tbl_athlete_stats_2,
tbl_athlete_stats tbl_athlete_stats_3,
tbl_athlete_stats tbl_athlete_stats_4,
tbl_athlete_stats tbl_athlete_stats_5,
tbl_athlete_stats tbl_athlete_stats_6,
tbl_athlete_stats tbl_athlete_stats_7,
tbl_athlete_stats tbl_athlete_stats_8,
tbl_athlete_stats tbl_athlete_stats_9
WHERE
tbl_athlete_stats_1.fld_player_number = tbl_teams.fld_athlete1 and
tbl_athlete_stats_2.fld_player_number = tbl_teams.fld_athlete2 and
tbl_athlete_stats_3.fld_player_number = tbl_teams.fld_athlete3 and
tbl_athlete_stats_4.fld_player_number = tbl_teams.fld_athlete4 and
tbl_athlete_stats_5.fld_player_number = tbl_teams.fld_athlete5 and
tbl_athlete_stats_6.fld_player_number = tbl_teams.fld_athlete6 and
tbl_athlete_stats_7.fld_player_number = tbl_teams.fld_athlete7 and
tbl_athlete_stats_8.fld_player_number = tbl_teams.fld_athlete8 and
tbl_athlete_stats_9.fld_player_number = tbl_teams.fld_athlete9;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Stuck on SQL - Any Takers?
@ 1999-03-19 15:22 Tom Lane <tgl@sss.pgh.pa.us>
1999-03-19 16:22 ` Re: [SQL] Re: Stuck on SQL - Any Takers? David Martinez Cuevas <david@estadistica.unam.mx>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 1999-03-19 15:22 UTC (permalink / raw)
To: pgsql-sql@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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] Re: Stuck on SQL - Any Takers?
1999-03-19 15:22 Re: Stuck on SQL - Any Takers? Tom Lane <tgl@sss.pgh.pa.us>
@ 1999-03-19 16:22 ` David Martinez Cuevas <david@estadistica.unam.mx>
0 siblings, 0 replies; 7+ messages in thread
From: David Martinez Cuevas @ 1999-03-19 16:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql@hub.org
On Fri, 19 Mar 1999, Tom Lane wrote:
> You can work around the bugs by setting GEQO to a smaller value, like
> about 6.
>
And how can i do it ??
Change the GEQO to a smaller value, and what does it means ??
I know GEQO is the genetic query optimizer but i didn't know it was
possible to configure it.
Thank you.
David Martinez Cuevas
Office 622-60-80 @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Home 565-25-17 "Eat Linux, Drink Linux... SMOKE LINUX "
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~1999-03-19 16:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-18 23:44 Stuck on SQL - Any Takers? Tim Perdue <perdue@raccoon.com>
1999-03-19 00:58 Re: [SQL] Stuck on SQL - Any Takers? Matthew Diesch <matthew@manufact.co.nz>
1999-03-19 02:32 Re: [SQL] Stuck on SQL - Any Takers? Tim Perdue <perdue@raccoon.com>
1999-03-19 03:16 ` twm139@its.to
1999-03-19 04:13 Re: [SQL] Stuck on SQL - Any Takers? Tim Perdue <perdue@raccoon.com>
1999-03-19 15:22 Re: Stuck on SQL - Any Takers? Tom Lane <tgl@sss.pgh.pa.us>
1999-03-19 16:22 ` David Martinez Cuevas <david@estadistica.unam.mx>
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