Re: [Postgres-xc-general] pgbench suggestion

[Koichi S. <koi...@gm...>]

pgxc_class has distribution definition of each table.    I'm using the
following views as a handy tool:

----
[koichi@linker:scripts]$ cat distr_view.sql
DROP VIEW IF EXISTS xc_table_distribution;


CREATE VIEW xc_table_distribution AS

SELECT pg_class.relname relation,
       CASE
         WHEN pclocatortype = 'H' THEN 'Hash'
         WHEN pclocatortype = 'M' THEN 'Modulo'
         WHEN pclocatortype = 'N' THEN 'Round Robin'
         WHEN pclocatortype = 'R' THEN 'Replicate'
         ELSE 'Unknown'
       END AS distribution,
       pg_attribute.attname attname
    FROM pg_class, pgxc_class, pg_attribute
    WHERE pg_class.oid = pgxc_class.pcrelid
          and pg_class.oid = pg_attribute.attrelid
          and pgxc_class.pcattnum = pg_attribute.attnum
 UNION

SELECT pg_class.relname relation,
       CASE
         WHEN pclocatortype = 'H' THEN 'Hash'
         WHEN pclocatortype = 'M' THEN 'Modulo'
         WHEN pclocatortype = 'N' THEN 'Round Robin'
         WHEN pclocatortype = 'R' THEN 'Replicate'
         ELSE 'Unknown'
       END AS distribution,
       '- none -' attname
   FROM pg_class, pgxc_class, pg_attribute
   WHERE pg_class.oid = pgxc_class.pcrelid
         and pg_class.oid = pg_attribute.attrelid
         and pgxc_class.pcattnum = 0
    ;

COMMENT ON VIEW xc_table_distribution IS
'View to show distribution/replication attribute of the given table.';

[koichi@linker:scripts]$
------

Info for pgbench tables are as follows:
-----------
koichi=# select * from xc_table_distribution
koichi-# ;
     relation     | distribution | attname
------------------+--------------+----------
 t                | Hash         | a
 pgbench_branches | Hash         | bid
 y                | Modulo       | a
 pgbench_tellers  | Hash         | bid
 pgbench_accounts | Hash         | bid
 x                | Round Robin  | - none -
 pgbench_history  | Hash         | bid
 s                | Replicate    | - none -
(8 rows)

koichi=#
------

Hope it helps.

Regards;
----------
Koichi Suzuki


2012/11/6 Tatsuo Ishii <is...@po...>:
>> One suggestion.   Because set bid value is relatively small, it may be
>> better to distribute tables using MODULO, not HASH.
>
> What is the distribution method when pgbench -k is used?
> --
> Tatsuo Ishii
> SRA OSS, Inc. Japan
> English: http://www.sraoss.co.jp/index_en.php
> Japanese: http://www.sraoss.co.jp