Re: getting the ranks out of items with SHARED

Am Mittwoch, 13. Juli 2005 15:35 schrieb Tom Lane:
> Janning Vygen <vygen(at)gmx(dot)de> 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