Skip site navigation (1) Skip section navigation (2)

Re: getting the ranks out of items with SHARED

From: Janning Vygen <vygen(at)planwerk6(dot)de>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Janning Vygen <vygen(at)gmx(dot)de>, pgsql-general(at)postgresql(dot)org
Subject: Re: getting the ranks out of items with SHARED
Date: 2005-07-14 16:16:28
Message-ID: 200507141816.28872.vygen@planwerk6.de (view raw or flat)
Thread:
Lists: pgsql-general
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


In response to

Responses

pgsql-general by date

Next:From: Janning VygenDate: 2005-07-14 16:17:45
Subject: Re: strange error with temp table: pg_type_typname_nsp_index
Previous:From: Alvaro HerreraDate: 2005-07-14 16:14:03
Subject: Re: ERROR: could not open relation

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group