Re: [Postgres-xc-general] pgbench suggestion

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

Yes, the view works at coordinator locally.

CREATE VIEW is propagated to all the coordinators so you should run it
only once at one of them.

The reason I suggested EXECUTE DIRECT is user table looked involved in
the query.

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


2012/11/6 Tatsuo Ishii <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