Am Mittwoch, 13. Juli 2005 15:35 schrieb Tom Lane:
> Janning Vygen <vygen@xxxxxx> writes:
> > this way it works:
> >
> > CREATE TEMP TABLE ranking AS *Q*;
> > EXECUTE 'UPDATE temp_gc SET gc_rank = ranking.rank
> > FROM ranking WHERE temp_gc.mg_name = ranking.mg_name;';
> >
> > and this way it doesn't:
> >
> > UPDATE temp_gc
> > SET gc_rank = ranking.rank
> > FROM (*Q*)
> > ranking
> > WHERE temp_gc.mg_name = ranking.mg_name;
>
> It's difficult to be sure without looking at EXPLAIN output, but I would
> guess that the second query is being done with a plan that involves
> multiple scans of "*Q*", and that's confusing your function.
>
> regards, tom lane
here you are. both versions with explain output
first version creates temp table (explain no 1) and updates afterwards
(explain no. 2).
second version combines both (explain no.3 )
[whats the best way to post explain output? My mailclient wraps the output. i
hope it is still readable]
no 1 ***** first create temp table *****
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Subquery Scan r1 (cost=58.54..70.99 rows=830 width=44) (actual
time=0.186..0.403 rows=7 loops=1)
-> Sort (cost=58.54..60.62 rows=830 width=44) (actual time=0.106..0.137
rows=7 loops=1)
Sort Key: gc_gesamtpunkte, gc_gesamtsiege, mg_name
-> Seq Scan on temp_gc (cost=0.00..18.30 rows=830 width=44) (actual
time=0.015..0.052 rows=7 loops=1)
Total runtime: 0.470 ms
(5 Zeilen)
no 2 **** update statement references temp table ****
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Merge Join (cost=127.70..193.49 rows=4109 width=70) (actual
time=0.221..0.404 rows=7 loops=1)
Merge Cond: ("outer".mg_name = "inner".mg_name)
-> Sort (cost=58.54..60.62 rows=830 width=66) (actual time=0.110..0.142
rows=7 loops=1)
Sort Key: temp_gc.mg_name
-> Seq Scan on temp_gc (cost=0.00..18.30 rows=830 width=66) (actual
time=0.013..0.055 rows=7 loops=1)
-> Sort (cost=69.16..71.63 rows=990 width=36) (actual time=0.089..0.119
rows=7 loops=1)
Sort Key: ranking.mg_name
-> Seq Scan on ranking (cost=0.00..19.90 rows=990 width=36) (actual
time=0.006..0.042 rows=7 loops=1)
Total runtime: 0.525 ms
(9 Zeilen)
no 3 **** combined update statement ****
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=167.70..232.14 rows=3445 width=78) (actual
time=0.455..0.774 rows=7 loops=1)
Merge Cond: ("outer".mg_name = "inner".mg_name)
-> Sort (cost=58.54..60.62 rows=830 width=66) (actual time=0.111..0.142
rows=7 loops=1)
Sort Key: temp_gc.mg_name
-> Seq Scan on temp_gc (cost=0.00..18.30 rows=830 width=66) (actual
time=0.016..0.057 rows=7 loops=1)
-> Sort (cost=109.16..111.23 rows=830 width=44) (actual time=0.248..0.281
rows=7 loops=1)
Sort Key: r1.mg_name
-> Subquery Scan r1 (cost=58.54..68.92 rows=830 width=44) (actual
time=0.102..0.201 rows=7 loops=1)
-> Sort (cost=58.54..60.62 rows=830 width=44) (actual
time=0.092..0.125 rows=7 loops=1)
Sort Key: gc_gesamtpunkte, gc_gesamtsiege, mg_name
-> Seq Scan on temp_gc (cost=0.00..18.30 rows=830
width=44) (actual time=0.008..0.045 rows=7 loops=1)
Total runtime: 0.886 ms
(12 Zeilen)
and this is the combined statement:
UPDATE temp_gc
SET gc_rank = ranking.rank
FROM (
SELECT
*,
ranking(r1.gc_gesamtpunkte, r1.gc_gesamtsiege) AS rank
FROM (
SELECT
mg_name,
gc_gesamtpunkte,
gc_gesamtsiege
FROM temp_gc
ORDER BY gc_gesamtpunkte DESC, gc_gesamtsiege DESC, mg_name ASC
) AS r1
) AS ranking
WHERE temp_gc.mg_name = ranking.mg_name;
to me it looks like the call to ranking() is moved from the subquery plan
"upwards". but i really can't interpret this explain output regarding to
"where" the ranking funcion is called.
my problem is that getting a rank out of items is very expensive with
aggregate functions, so i try to do a trick here which is not very relational
indeed and this of course can lead to trouble.
Maybe i have to rethink the whole stuff. (because i get some temp table errors
anyway as mentioned in the other thread)
Kind regards,
Janning Vygen
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
