You can use hint using pg_plan_hint for PostgreSQL.

Sourceforge.JP offers pg_plan_hint for PostgreSQL for free.

In my previous post, I mentioned about a hint for PostgreSQL. But, if you just need hint feature in EDB(enterprise database), the cost of EDB might be a little expensive. And, according to http://wiki.postgresql.org/wiki/OptimizerHintsDiscussion, postgresql has no plan about hint. So, if you just need the hint feature, you can consider the pg_plan_hint. The pg_plan_hint is provided by sourceforge.JP. The usage of pg_plan_hint is similar with oracle. Actually, I don’t know, pg_plan_hint is bugfree. But, through my simple test, I think that pg_plan_hint can use in simple case. For the test, first of all, Let’s install the pg_plan_hint.

1) Download pg_plan_hint
http://en.sourceforge.jp/projects/pghintplan/
In my case, I downloaded “pg_hint_plan92-1.0.0.tar.gz” file.

2) File upload and extract file
> ftp hostip
user: ****
password: ***

$ gunzip pg_hint_plan92-1.0.0.tar.gz
$ tar xvf pg_hint_plan92-1.0.0.tar

3) Installation
$ cd pg_hint_plan92-1.0.0
--The value of PG_CONFIG is maybe diffent, depend on your environment.
$ make PG_CONFIG=/usr/local/pgsql/bin/pg_config
$ make PG_CONFIG=/usr/local/pgsql/bin/pg_config install

If you already installed PGXC, you meet the compile error like this.
pg_hint_plan.c:1554: warning: passing arg 6 of pointer to function makes integer from pointer without a cast
pg_hint_plan.c:1554: error: too few arguments to function
make: *** [pg_hint_plan.o] Error 1

Then, you just edit /usr/local/pgsql/include/server/tcop/utility.h for fix error.

$ vi /usr/local/pgsql/include/server/tcop/utility.h
#ifndef UTILITY_H
#define UTILITY_H
#include "tcop/tcopprot.h"
#undef PGXC -- add this line

-- Edit postgresql.conf file
$ cd /usr/local/pgsql/data/datanode1/
$ vi postgresql.conf
shared_preload_libraries='pg_hint_plan' -- add this line

-- restart pgsql
$ pg_ctl stop -D /usr/local/pgsql/data/datanode1 -m fast
$ pg_ctl start -D /usr/local/pgsql/data/datanode1 -Z datanode -l logfile

From now, we can use hint for pgsql. Let’s do a test.

(1) Create table/index and data loading.

create table t1 (c1 int, c2 int, c3 int, dummy char(100));
create index t1_idx1 on t1 (c1, c2, c3);
create index t1_idx2 on t1 (c2, c3);
create index t1_idx3 on t1 (c3);
create index t1_idx4 on t1 (c1); 
 
create table t2 (c1 int, c2 int, c3 int, dummy char(100));
create index t2_idx1 on t2 (c1, c2, c3);
create index t2_idx2 on t2 (c2, c3);
create index t2_idx3 on t2 (c3);
 
create table t3 (c1 int, c2 int, c3 int, dummy char(100));
create index t3_idx1 on t3 (c1, c2, c3);
create index t3_idx2 on t3 (c2, c3);
create index t3_idx3 on t3 (c3); 
 
insert into t1 select 1, mod(c1,100), mod(c1,1000), 'dummy' from generate_series(1,100000) c1;
insert into t2 select 1, mod(c1,100), mod(c1,1000), 'dummy' from generate_series(1,10000) c1;
insert into t3 select 1, mod(c1,100), mod(c1,1000), 'dummy' from generate_series(1,100) c1;

(2) The test of IndexScan and SeqScan hints.
In my test case, IndexScan and SeqScan hints are working very well.

/*+ IndexScan(t1 t1_idx1) */
explain analyze
select *
from   t1
where  c1=1
and    c2=10
and    c3=100

"Index Scan using t1_idx1 on t1  (cost=0.00..8.29 rows=1 width=113) (actual time=0.021..0.021 rows=0 loops=1)"
"  Index Cond: ((c1 = 1) AND (c2 = 10) AND (c3 = 100))"
"Total runtime: 0.056 ms"

/*+ IndexScan(t1 t1_idx4) */
explain analyze
select *
from   t1
where  c1=1
and    c2=10
and    c3=100

"Index Scan using t1_idx4 on t1  (cost=0.00..4923.26 rows=1 width=113) (actual time=99.512..99.512 rows=0 loops=1)"
"  Index Cond: (c1 = 1)"
"  Filter: ((c2 = 10) AND (c3 = 100))"
"Total runtime: 99.556 ms"

/*+ SeqScan(t1) */
explain analyze
select *
from   t1
where  c1=1
and    c2=10
and    c3=100

"Seq Scan on t1  (cost=0.00..3536.00 rows=1 width=113) (actual time=49.532..49.532 rows=0 loops=1)"
"  Filter: ((c1 = 1) AND (c2 = 10) AND (c3 = 100))"
"Total runtime: 49.571 ms"

(3) The test of Leading, NestLoop, HashJoin hints.
In my test case, all hints were working very well.
And the usage of NestLoop and HashJoin hints were are little different with oracle. Oracle uses one or two argument in use_nl and use_hash hints. For example, I want to use nested loops join with a, b, c table in order. In that case,
we use /*+ leading(a b c) use_nl(b) use_nl(c) */ or /*+ leading(a b c) use_nl(a b) use_nl(b c) */.
But, in case of pg_plan_hint, we use /*+ leading(a b c) NestLoop(a b) NestLoop(a b c) */.

/*+ Leading(c b a) NestLoop(c b) HashJoin(c b a) */
explain analyze
select a.*, b.*, c.*
from   t1 a, t2 b, t3 c
where  a.c1=b.c1
and    a.c2=b.c2
and    a.c3=b.c3
and    b.c1=c.c1
and    b.c2=c.c2
and    b.c3=c.c3
 
"Hash Join  (cost=504.08..4415.18 rows=10 width=339) (actual time=6.825..342.277 rows=100000 loops=1)"
"  Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2) AND (a.c3 = b.c3))"
"  ->  Seq Scan on t1 a  (cost=0.00..2786.00 rows=100000 width=113) (actual time=0.014..80.097 rows=100000 loops=1)"
"  ->  Hash  (cost=503.91..503.91 rows=10 width=226) (actual time=6.754..6.754 rows=1000 loops=1)"
"        Buckets: 1024  Batches: 1  Memory Usage: 244kB"
"        ->  Nested Loop  (cost=0.00..503.91 rows=10 width=226) (actual time=0.037..4.971 rows=1000 loops=1)"
"              Join Filter: (b.c1 = c.c1)"
"              ->  Seq Scan on t3 c  (cost=0.00..3.00 rows=100 width=113) (actual time=0.009..0.098 rows=100 loops=1)"
"              ->  Index Scan using t2_idx2 on t2 b  (cost=0.00..4.99 rows=1 width=113) (actual time=0.011..0.028 rows=10 loops=100)"
"                    Index Cond: ((c2 = c.c2) AND (c3 = c.c3))"
"Total runtime: 391.877 ms"
  
/*+ Leading(a b c) HashJoin(a b) NestLoop(a b c) */
explain analyze
select a.*, b.*, c.*
from   t1 a, t2 b, t3 c
where  a.c1=b.c1
and    a.c2=b.c2
and    a.c3=b.c3
and    b.c1=c.c1
and    b.c2=c.c2
and    b.c3=c.c3
 
"Nested Loop  (cost=625.00..14466.80 rows=10 width=339) (actual time=22.144..6251.758 rows=100000 loops=1)"
"  Join Filter: ((a.c1 = c.c1) AND (a.c2 = c.c2))"
"  ->  Hash Join  (cost=625.00..11600.00 rows=10000 width=226) (actual time=22.114..1928.040 rows=1000000 loops=1)"
"        Hash Cond: ((a.c1 = b.c1) AND (a.c2 = b.c2) AND (a.c3 = b.c3))"
"        ->  Seq Scan on t1 a  (cost=0.00..2786.00 rows=100000 width=113) (actual time=0.013..93.049 rows=100000 loops=1)"
"        ->  Hash  (cost=279.00..279.00 rows=10000 width=113) (actual time=22.019..22.019 rows=10000 loops=1)"
"              Buckets: 1024  Batches: 2  Memory Usage: 669kB"
"              ->  Seq Scan on t2 b  (cost=0.00..279.00 rows=10000 width=113) (actual time=0.008..8.505 rows=10000 loops=1)"
"  ->  Index Scan using t3_idx3 on t3 c  (cost=0.00..0.27 rows=1 width=113) (actual time=0.002..0.002 rows=0 loops=1000000)"
"        Index Cond: (c3 = a.c3)"
"Total runtime: 6308.625 ms"
 
/*+ Leading(a b c) NestLoop(a b) NestLoop(a b c) */
explain analyze
select a.*, b.*, c.*
from   t1 a, t2 b, t3 c
where  a.c1=b.c1
and    a.c2=b.c2
and    a.c3=b.c3
and    b.c1=c.c1
and    b.c2=c.c2
and    b.c3=c.c3
 
"Nested Loop  (cost=0.00..14722.40 rows=10 width=339) (actual time=0.067..8733.899 rows=100000 loops=1)"
"  Join Filter: ((a.c1 = c.c1) AND (a.c2 = c.c2))"
"  ->  Nested Loop  (cost=0.00..11855.60 rows=10000 width=226) (actual time=0.055..4084.912 rows=1000000 loops=1)"
"        Join Filter: (a.c1 = b.c1)"
"        ->  Seq Scan on t2 b  (cost=0.00..279.00 rows=10000 width=113) (actual time=0.014..10.070 rows=10000 loops=1)"
"        ->  Index Scan using t1_idx2 on t1 a  (cost=0.00..1.14 rows=1 width=113) (actual time=0.017..0.232 rows=100 loops=10000)"
"              Index Cond: ((c2 = b.c2) AND (c3 = b.c3))"
"  ->  Index Scan using t3_idx3 on t3 c  (cost=0.00..0.27 rows=1 width=113) (actual time=0.002..0.003 rows=0 loops=1000000)"
"        Index Cond: (c3 = a.c3)"
"Total runtime: 8791.946 ms"

PostgreSQL advanced server supports some optimizer hints.

Optimizer Hints are very useful feature for query performance tuning.

There is diversity of opinion that use of optimizer hints. But, It’s true that optimizer hints are very convenient way to statement tuning. So, almost DBMS vendors support optimizer hint. But, PostgreSQL doesn’t suppot optimizer hint. Instead of, PostgreSQL supports “planner method configuration”. For example, in case of join method, PostgreSQL supports “enable_hashjoin”, “enable_nestloop”, “enable_mergejoin”. So, if you need more than two join methods in a statement, “planner method configuration” maybe can’t control query execution plan that you want. That is very dangerous from a performance perspective.

Fortunately, PostgreSQL advanced server supports some optimizer hints. For example, ORDERED, INDEX, NO_INDEX, FULL, FIRST_ROWS, USE_NL, USE_HASH and so on. That is very basic hints for join order, access method and join method. And, there isn’t hints for sub query, view merge, push predicate and so on. But, basic hints can cover a lot of bad sql statement. Below is the result of my simple test.

postgreSQL-XC 1.0.2 installation guide.

The postgreSQL-XC is simple and powerful cluster solution. My environment for installation is below that.

• # of database server –> 2 linux servers
• # of datanode in each database server –> 2 ( total 4 datanodes in cluster)
• # of coordinator in each database server –> 2
• # of gtm in each database server –> 2
• postgreSQL version –> 9.1.7

pre-installation
if postgreSQL is not installed on server then you must install postgreSQL.

1) Download postgreSQL
Download postgresql-9.2.3.tar.gz

2) Install postgreSQL
— upload tar file to server using FTP.
— and login to linux server (using postgres o/s user or any other user can)

$ gunzip postgresql-9.2.3.tar.gz
$ tar xvf postgresql-9.2.3.tar
$ cd postgresql-9.2.3
$ ./configure
$ make
$ su root
Password: *** * ( switch user to root for install)
# make install