[MUD-Dev] Player run reputation system

[Trump]

On Tuesday 03 July 2001 08:57 am, J Todd Coleman wrote:

> I'd also be curious to see what happens when you add more than two
> tables to your test case. What happens to the cost hit when you
> include a third table, or a fourth?  Is it still 2x the time, or
> does the multiplier continue to increase as well?  

Here is a 4 table join.  If I rember correctly the 2 table join had
a cost of 160, the single table selects were running in the 30-60
range.  But the original test was a count, and therefore slower.

This query, at a cost of 105, appears to user as fast as: print
"hello world";

To say this 4 table join takes minimal cpu power would be to highly
overestimate it's cost.


  ng=# select personal.state, referral.referdt, sum 
  (transactions.amount), site.sd from personal, referral,
  transactions, site where personal.uid=4 and
  site.uid=transactions.uid and site.uid=personal.uid and
  site.rid=referral.rid group by personal.state, referral.referdt,
  site.sid;

   state |        referdt         |  sum   | siteid
  -------+------------------------+--------+--------
   IA    | 2000-11-18 02:34:21-08 | 159.96 |     16
  (1 row)
 
  ng=# explain select personal.state, referral.referdt, sum
  (transactions.amount), site.sd from personal, referral,
  transactions, site where personal.uid=4 and
  site.uid=transactions.uid and site.uid=personal.uid and
  site.rid=referral.rid group by personal.state, referral.referdt,
  site.sid;

  NOTICE:  QUERY PLAN:
 
  Aggregate  (cost=105.65..105.66 rows=0 width=56)
    ->  Group  (cost=105.65..105.65 rows=1 width=56)
      ->  Sort  (cost=105.65..105.65 rows=1 width=56)
        ->  Nested Loop  (cost=0.00..105.64 rows=1 width=56)
          ->  Nested Loop  (cost=0.00..101.01 rows=1 width=44)
            ->  Nested Loop  (cost=0.00..58.88 rows=3 width=32)
              ->  Index Scan using personal_userid_ccid_idx
              on personal  (cost=0.00..8.60 rows=2 width=16)
              ->  Index Scan using transactions_userid_idx
              on transactions  (cost=0.00..31.33 rows=8 width=16)
            ->  Index Scan using site_userid_idx on site  
            (cost=0.00..16.70 rows=4 width=12)
          ->  Index Scan using referral_referid_idx on referral  
          (cost=0.00..4.62 rows=1 width=12)
 
  EXPLAIN
_______________________________________________
MUD-Dev mailing list
MUD-Dev at kanga.nu
https://www.kanga.nu/lists/listinfo/mud-dev