Re: [Postgres-xc-general] pgbench suggestion

[Tatsuo I. <is...@po...>]

Thanks. Your view works great! BTW, Should the view be executed on
coordinator node? If so, I'm a little bit confused. Because you said:

> Because pgxc_class and pg_class are both local to each node, oid is
> also local.   To query correctly, they should be issued using EXECUTE
> DIRECT statement.   Each EXECUTE DIRECT will return the result local
> to each node.
> 2012/11/6 Tatsuo Ishii <is...@po...>:
>> I sent a query to coordinator node and got following result. Doesn't
>> this means table "pgbench_accounts" distributed?
>>
>> test=# select * from pgxc_class as x join pg_class as c on x.pcrelid = c.oid where c.relname = 'pgbench_accounts';

> 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