Monday, June 25, 2012

pg_service.conf in redhat

I'm toying around with pg_service.conf. It wasn't obvious - but I was able to determine that if you add pg_service.conf to /etc/sysconfig/pgsql/ it will get picked up globally.

Thursday, June 14, 2012

psql - reverse search history

psql the postgresql command line client is usually compiled with libreadline support. As a result of this you can use some neat functionality such as reverse search by hitting ctl-r within the client.
report=#  
hit CTL-r
(reverse-i-search)`': 
start typing
(reverse-i-search)`sel':  select * from report.audit where 

Wednesday, June 6, 2012

Create a backup manifest in PostgreSQL with oid2name

oid2name is a nifty little program comes with postgresql an it allows you to take those obscure relfilenode named datafiles at the OS level and map them to database objects.

The only downside to this little tool is that it doesn't exactly work when your database is down since it uses pg_* to figure out this info. So if you're in a situation where you're restoring the database and find out that you're missing a file from the backup - you may want to know what table is lost.

If you thought ahead and have a manifest.txt in your backups you'll be able to see that the following maps to pgbench_accounts
-rw------- 1 postgres postgres 13434880 Jun  7 04:44 /db/9.1/data/base/1952441/1952454


If you use the -ix flag you get schema, OID and tablespace. If you also add the -S flag you get all the system tables too which is very important in a recovery situation.
/usr/pgsql-9.1/bin/oid2name -ix -d pgbench
From database "pgbench":
  Filenode             Table Name      Oid  Schema  Tablespace
--------------------------------------------------------------
   1952454       pgbench_accounts  1952448  public  pg_default
   1952459  pgbench_accounts_pkey  1952459  public  pg_default
   1952442       pgbench_branches  1952442  public  pg_default
   1952455  pgbench_branches_pkey  1952455  public  pg_default
   1952451        pgbench_history  1952451  public  pg_default
   1952445        pgbench_tellers  1952445  public  pg_default
   1952457   pgbench_tellers_pkey  1952457  public  pg_default

Friday, June 1, 2012

enable_material=false

I remember back in my oracle days, one of the first things I'd turn off in my OLTP environments was hash_join_enabled. Oracle would spend more time creating the hash table than it would actually processing the query.

I ran into that again this week, but this time with Postgres 9.1 and this time with Materialize.

Here's the query stats (from the wonderfull http://explain.depesz.com) pretty clearly showed me where the problem was.
node typecountsum of times% of query
Bitmap Heap Scan561251.792 ms0.1 %
Bitmap Index Scan6666.669 ms0.0 %
BitmapAnd10.225 ms0.0 %
Hash64.904 ms0.0 %
Hash Join637540.586 ms0.1 %
HashAggregate198.755 ms0.0 %
Index Scan53777.276 ms0.0 %
Materialize928084279.438 ms54.9 %
Merge Left Join1100.578 ms0.0 %
Nested Loop7607.531 ms0.0 %
Nested Loop Left Join1022904169.450 ms44.8 %
Seq Scan1572686.868 ms0.1 %
Sort3137.154 ms0.0 %


Here's the same query in PG9.0.

node typecountsum of times% of query
Bitmap Heap Scan3238.963 ms0.1 %
Bitmap Index Scan441.682 ms0.0 %
BitmapAnd10.458 ms0.0 %
Hash1529947.841 ms9.4 %
Hash Join773363.019 ms22.9 %
Hash Left Join841505.547 ms13.0 %
HashAggregate1164.592 ms0.1 %
Index Scan6187.713 ms0.1 %
Materialize15122.977 ms1.6 %
Nested Loop685.415 ms0.0 %
Nested Loop Left Join310780.593 ms3.4 %
Seq Scan16158396.545 ms49.5 %
Sort115.215 ms0.0 %


It seems like PG9.1 favors materializing a little more than 9.0 does, I even knocked the statistics up to 10k in the PG9.1 environment to get it to knock that off but no luck.

To get by the problem, while we tune the query and look at some other PG tuning options, I just disabled materializing for this specific application user.
Web Statistics