Re: getting the ranks out of items with SHARED - Mailing list pgsql-general

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