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

Advertisements

About Ashish Yadav

Hey there!!! I am Ashish Yadav, a Geek | Music Lover | Open Web enthusiast | Wannabe entrepreneur. I currently live in Chennai, India and share my thoughts, nifty hacks rather infrequently here with rest of the world. If you like my posts, may I suggest subscribing to the blog to read them as they are written :) . As a part of my day job I enjoy writing ubercool python code, and have loads of fun while at it. Opinions presented here are mine and not to be associated with my employer or anyone else. Opinions presented here are mine and not to be associated with my employer or anyone else.
This entry was posted in Hacks, Technology and tagged , , , , , . Bookmark the permalink.

One Response to PostgreSQL tips and tricks !! (Part 1)

  1. rockwell says:

    I feel more folks will need to read this, really good info.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s