PostgreSQL tips and tricks !! (Part 1)

For a long time I wanted to have an online catalog of various sql nifty queries and hacks that i have found helpful and continue to use till date .
Many times  I got help from various blogs written on this topic.
Now It’s my time to give back something to the community, I learned so much from .

This post is most useful for people who have intermediate to advanced knowledge of postgresql/SQL.

Note : Please use these queries at your own risk, hopefully it should help you get your work done without any hiccups

1) Using query aliases in table joins for better performance:
While doing a join between two (or more ) tables, using aliases gives gives performance gain over ordinary joins in certain cases. Like for ex.
Suppose there are two tables having Bank information (bankinfo) and Transaction Information (trinfo) , They share a common column named IFSC number.
I need Bank Information,and total number of transactions present for each bank. Now I can write needed sql query in two ways.

select bankinfo.name,trinfo.ifsc,count(*) from bankinfo left outer join trinfo on bankinfo.ifsc=trinfo.ifsc group by bankinfo.name,trinfo.ifsc;

Or using query aliases

select bankinfo.name,q.count from bankinfo left outer join (select ifsc,count from trinfo group by ifsc)q on bankinfo.ifsc = q.ifsc;

You can see that we are reducing rows that would need to be joined, which will most of the times will result in improved performance.This mostly works if join involves one of the tables which is relatively large in size or if you are using group by clauses.

2)Using multicolumn indexes if you are using a same set of multiple columns in all your queries.
This improves query performance drastically for some cases. Use multicolumn indexes wisely because they need more space/time resources to keep them updated !!.
You can read more about them on the well-maintained Postgresql Online docs.

3)Calculate Database, Tablewise disk space sizes.

• Total Database Size:

select pg_size_pretty(pg_database_size(‘#database_name#’));

• Size per table ordered by largest size descending:

select n.nspname, c.relname, pg_size_pretty(pg_total_relation_size(c.relname)) FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN (‘r’,”) AND n.nspname NOT IN (‘pg_catalog’, ‘pg_toast’)
AND pg_catalog.pg_table_is_visible(c.oid) order by pg_total_relation_size(c.relname) desc;

4) Calculate nth highest column value in a  table:

select rank from students order by rank asc offset n limit 1;

5)Using set operators in your queries:

Using Set Operators In SQL queries is handy at times.

Sometimes using ” except ” instead of  highly expensive “  not in “ operator can reduce query execution time many folds.

For ex : To check which bank branch don’t have deposits transaction for amount more than 100k in last week:

Using not in clause:

select branch_name from branches where branch_id not in (select branch_id from transactions where type=’deposit’ and transaction_time > interval ’1 week’  and amount > 1000000)

using Except clause:

select branch_name where branch_id in (select branch_id from branches except select branch_id from transactions where type=’deposit’ and transaction_time > interval ’1 week’  and amount > 1000000)

In Detail you can refer to : http://www.postgresql.org/docs/8.2/static/queries-union.html

Always Remember – When in Doubt use Explain Analyze to see the query cost estimates to come up with the right query.

Links,Tools I have found to be useful:
Postgresql Query plan Analyzer tool
Sql Pretty Formatter
Some Cool PostgreSQL tricks