MySQL query with the conditions as ‘NOT LIKE IN’

While working with MySQL database i needed to write a query which will exclude rows where a column contains one or more words from a set of words. i.e I want to fetch data from student from table where name doesn’t contain words shashank,john etc.

Initially i was trying to write a query like this  :

select * from TABLE
where
Column NOT Like IN
('%VALUE1%', '%VALUE2%', '%VALUE3%') 

but i was getting error as IN doesn’t work with Like.

There is two way you can write this query.

Method 1 :

Add multiple Like statement, each for every word to be excluded.

Select * from TABLE
where Column NOT Like '%VALUE1%'
AND Column NOT Like '%VALUE2%'
AND Column NOT Like '%VALUE3%' 

Method 2 :

If you have set of words which you want to include/exclude in search from a particular column. You may want to use regular expression function of mysql.

Exclude set of words from a column :

SELECT
*
FROM
TABLE
WHERE
Column NOT REGEXP 'VALUE1|VALUE2|VALUE3' 

Search set of words in a column :

SELECT
*
FROM
TABLE
WHERE
Column REGEXP 'VALUE1|VALUE2|VALUE3'

switching back to text console error during centos installation

I was trying to dual-boot CentOS 6 on my laptop that is currently running Windows 8. I have two storage devices, an SSD that has my Windows installation, and an HDD that has all of my data. Both are formatted using GPT, and Windows boots using UEFI. I used the CentOS 6.5 live CD (CentOS-6.5-x86_64-LiveCD.iso) to create an EFI-bootable flash drive (it does boot properly in EFI mode). I receive an error, however, when CentOS is booting (error is below).

Kernel panic - not syncing: Attempted to kill init!
Pid: 1, comm: init Not tainted 2.6.32-431.el6.x86_64 #1
Call Trace:
[<ffffffff815271fa>] ? panic+0xa7/0x16f
[<ffffffff81077622>] ? do_exit+0x862/0x870
[<ffffffff8118a865>] ? fput+0x25/0x30
[<ffffffff81077688>] ? do_group_exit+0x58/0xd0
[<ffffffff81077717>] ? sys_exit_group+0x17/0x20
[<ffffffff8100b072>] ? system_call_fastpath+0x16/0x1b
drm_kms_helper: panic occurred, switching back to text console

Solution : To fix this, go under the EFI folder in your USB stick, then find those files ending with *.conf and use a text editor to change the root= to your USB device. In my case, it is some things like live:UUID=UUID_OF_Partition. How to find UUID of a USB Drive : you can use the following command if you have a linux system.

#sudo blkid
/dev/sda1: UUID="69C2-59F8" TYPE="vfat"
/dev/sda2: UUID="3378cbd8-7df1-451f-b475-d3dbf95edf35" TYPE="ext4"
/dev/sda3: UUID="dd505d50-43e8-4d5c-9543-4df4edbff790" TYPE="swap" 

Or you can go the properties of your USB Drive & name will be the UUID of that USB Drive.
Name :   UUID=69C2-59F8

Then your content to put in *.conf  files will become : live:UUID=69C2-59F8. Another note to take care is instead of install using UNetBootIn or ISO2USB, we can actually use Fedora Live USB Creator. The good thing about use linux is that you can find your USB device’s UUID under syslinux\syslinux.cfg.

MS DHONI INTERVIEW

You would be forgiven for thinking that winning the World Cup was MS Dhoni’s finest moment. After all, that wonderfully free hit over mid-on for six – a stroke of majesty and triumph to close the deal – lifted a heavy burden from the shoulders of all India. The young were sick of hearing about the old, hearing tales of Kapil Dev’s famous band who had won the World Cup in England in 1983. India’s youth finally had its moment and, best of all, had it at home, in the city of Gavaskar and Tendulkar, and from the hand of a captain whose swashbuckling style had made him as much a darling in the grand homes of Kolkata as in the fishing villages of Kerala.

MS and me are off for dinner. He is wearing trainers, jeans sawn off at the knee, a T-shirt and a camouflage jacket. We turn left out of the team hotel and left again down an alleyway that leads us immediately into the central square of another provincial English town. After a third left and a stroll past Nando’s – he wants a break, he says – we enter another chicken-type diner that he says is pretty good. During this short walk, the Indian captain is asked to pose for 25 photographs. To the first family of five he says no, but adds that he will be back a little later and will happily answer their call then. Fifty metres later he relents. Of course, five means 25. “Rahul [Dravid] taught me how to say no politely,” he says, “but in the end, when it is only a few people, why not.”

In the diner, the manager’s face lights up. Dhoni hands him a signed Indian shirt. Waiters gather to look. The place is near empty. We sit at a small table and drink orange juice. He is an unbelievably good-looking man. A woman comes over with her daughter. She shoves her forward and Dhoni gives a nice smile for the ‘snap’. The woman cannot contain herself and, puce with embarrassment, asks if she can do the same. “No problem,” he says. The husband comes over too. You’ve probably got the picture by now. The chicken arrives.

“Winning the World Cup was very special because it meant so much to so many. One thing about our country that is constant is cricket. The smile it brought to people’s faces was the thing I shall always remember. It reminded me, reminded all of us, of our importance to the lives of the Indian people less lucky than we are.

“But personally, no. The proudest moment of my own life was the day I accepted my ranks at the Pipping Ceremony.” Your ranks? Pray explain. “From when I was a young boy, I read books on weapons and tanks and combat. I was so interested in the army. Then later, my profile as an Indian cricketer allowed me access to the real thing so I spent time with soldiers, learning about their lives and sacrifices. I had friends in the Special Forces and heard their stories of missions, the risks they take, the commitment they must have to succeed. I met the chief of the Army Staff and told him about my lifelong fascination with the military. I asked if it was possible to become involved myself. Incredible as it may seem, he took a recommendation through various channels that finished up with the president of the country. The result of all this is that I became an honorary lieutenant colonel in the Parachute Regiment.” The things we don’t know!

More of the army in a minute. “I love my country,” he says. “I tell my wife she is only the third most important thing after my country and my parents, in that order. The point is that while I am an Indian cricketer I will devote myself to that cause. Cricket is not everything, not by any means, but it is a large part of who I am. Therefore I want to play in all formats of the game and to play as much as possible because before long it will be over. Then I’ll focus more on the Army!”

Does his leadership have any ideological ambition, I wonder? “I believe in the process more than the result. If you are properly prepared, physically and mentally, committed to the task and fully engaged in the moment, then I have no problem with the outcome. It is not about 95 per cent at this level and with this much exposure, it is about 100 per cent. A dropped catch is fine if you were focused and in the moment. It becomes a problem to me if you are not. I live for the moment, not the past, not the future. People ask about my remaining ambitions. They are simple. I want to keep playing well and to win. I want the Indian people to be proud of us.”

*********************************************************

As Dhoni talks, many aspects of his character which are otherwise well concealed begin to emerge. He is a thoughtful and intelligent person. He is driven by a deep morality. He is honest, he is loyal. And he does not suffer fools. Perhaps above all, he is utterly single-minded.

Dhoni Hits the winning six  Credit : INDRANIL MUKHERJEE/AFP/Getty Images

Dhoni Hits the winning six Credits : INDRANIL MUKHERJEE/AFP/Getty Images

“My wife wants me to eat fish, she says it is delicious. But I don’t like fish, so that is that. I find liquor has a bitter taste, so I don’t drink alcohol but understand that other people enjoy it. I care most about how people live their lives, what choices they make and how they get the best from themselves. This is why people such as Amitabh Bachchan [Indian actor], Sachin and Rahul are heroes of mine. And I love young talent, Ajinkya Rahane for instance, and urge him to be himself, to trust his talent and allow it to work for him within the parameters of his capabilities. He did that at Lord’s and the hundred he made was among the best I have seen by any Indian batsman.”

Given Dravid is working for television on the series, I seek him out. “I cannot speak highly enough of MS. He has a unique ability to ignore consequences and soak up pressure. This makes it easier for everyone else. When he took over the captaincy it was immediately obvious that he held the seniors in great respect but was not overawed by them. He is calm and measured. Win, lose, he can walk away. I don’t know many, if any, who can retain their perspective like he can.”

Dhoni’s father was strict, which taught him respect – “and punctuality” – above all else. “When I was young I was closer to my mother but while I was in Class 12 (18 years old) the reason for my father’s reference to discipline became clear to me. Exams were due and I needed to study but the last day of preparation coincided with a big cricket match. I asked him what I should do. In turn, he asked if I was ready for the exam, which I felt I was. ‘With only 24 hours to go you will either be ready or you will not. Go and play,’ he said, ‘and have some fun for there is nothing more you can do in your studying.’ In other words, you now have your priorities in order.”

Dhoni and his beautiful wife, Sakshi Singh, live with his parents in Ranchi. True. The man listed on the Forbes 100 list as the 22nd richest sportsperson in the world lives with Mum and Dad. How cool is that. Twelve years ago he was working for the railways as a ticket collector. Now he could buy a jet plane. “There is some myth to the railway story. We are a middle-class family and my luck to have some talent as a sportsman meant I qualified for a Sports Quota (like a government scholarship). This gave me a proper job, for life had I wanted it, and it allowed me to earn some money, gain some life experience but also to focus on cricket. I thought I was reasonably good but didn’t think I would get this far. Every morning for four years, eight months a year, we practised at 6.30am – always with two new balls and very competitively. We were taught to bat for 20 minutes without getting out and then to challenge ourselves with game situations to which we had to adapt.

“When we were kids, our sport was dependent on weather. When the sun shone it was cricket. If not we played football. I was a goalkeeper but always played knockabout matches up front because I wanted to run around. When it rained we played table tennis and badminton. I just loved to compete. If I couldn’t, I wouldn’t play! Studies were important but sport was a passion.”

Back to the World Cup then and the innings that won it, the best one-day innings I have seen. Was it a massive gamble to promote himself above Yuvraj Singh, especially as he had relatively little batting during the tournament? “Something in me said I should go. I was changing into my batting shoes when Gary [Kirsten] came in and gave me a look. ‘Should I go?’ ‘Yes, I think you should go, man,’ he said. I had played a lot with Murali for Chennai in the IPL and felt I could read him. If I got it right, I felt I would calm the others down. You ask if it was a gamble. I believe that it is not about a right or wrong decision but about an honest and well thought through reason. I knew in my heart it was justified and felt that the team trusted me.”

He much enjoyed working with Gary Kirsten, who gave them all confidence. Their trust was implicit, as it is with Duncan Fletcher. “We are as one, it has to be that way. Gary had a fantastic skill to work with a player on a weakness without the player knowing it is a weakness. Indian people run on emotion and both he and Duncan have learnt to respond to that in their own way. Duncan is a little more old-fashioned than Gary and gets frustrated by things he sees as unprofessional but he is a very good man and there is more to him than meets the eye – humour for a start.”

*********************************************

We turn to life away from the game. He listens to Hindi music – “70’s and 80’s stuff mainly, it had more meaning to it then, the songs were deeper and more patriotic. I like reggae too, the beat works for me.” That passion for sport is matched by another: a devotion to his collection of 35 motorbikes.

“I love them all, expensive, cheap, old and new,” he says. A Harley? “Yes, a Harley Fat Boy. A Ducati Pantah, a Hellcat – one of the confederate motorcycles from the US, some ancient two-strokes, Triumphs, even an old BSA.” Does he ride them hard? “Not really. Well sometimes, but actually, I like to polish them. I’ll tell you a story. When I was a boy, one of our senior players used to position his beautiful bike behind me and then hit balls at me to catch. If I missed one and it hit the bike, I had to fix up the mark. Therefore I learned not to miss many. I’ve either been polishing bikes or trying to cling on to catches pretty much ever since!”

If you stop to think a while about the 33-year-old Mahendra Singh Dhoni, you can only marvel. This man leads the Indian cricket team under the greatest burden of expectation. He keeps wicket and bats in a key middle-order position in all three formats of the game. He has led India to all three ICC trophies and is a serial winner of the IPL with the Chennai Super Kings. He endorses 21 wide-ranging, high-profile products that include Pepsi, Reebok, Brylcreem, TVS Motors and Reliance. They say he is worth US$51 million. He has been awarded the fourth highest civilian honour in India, the Padma Shri, has been the ICC World Cricketer of the Year and is co-owner of the Mahi Racing team. All this, and, best of all, he likes to stay home and walk the dogs.

Conversation with Dhoni is stimulating. Unlike so many sportsmen, he avoids clichés. Better still, he engages. His interest is more in the person than the performance and his hope is to be remembered as a human being every bit as much as a cricketer.

The clarity of his perspective tells us much about the über-cool figure who finishes matches to such effect. “The problem for the finisher is that, too often, there is not much else to come. The big shot is not necessarily the best or smartest shot. What matters is staying in. I am never in a hurry. My aim is to leave it till the bowler and me are on level ground, i.e. to the point at which he is under as much pressure as me. Then we see who can handle it. And most crucially, to stay humble. Assume nothing. Each day is different. I always tell the team that today somebody will be a hero. You have the chance, go and take it.”

MS is an honorary lieutenant colonel in the Parachute Regiment.

MS is an honorary lieutenant colonel in the Parachute Regiment.

Perhaps there is a little military discipline in these performances. Perhaps the Army has rubbed off .
“Yes, maybe. I have made a real study of manoeuvres, of geography and physics, and of mental application to various challenges. I had the choice of four regiments but chose a fifth, the Parachutes, because it is a hundred per cent volunteer regiment. They are derived from the British SAS – an incredible unit, you know – and I felt I would be able to contribute more with them later in life. I believe I know enough to justify the honour and was worthy of it. One day I will train with them and I will jump with them too because I am keen to truly earn my wings.”

We move back to cricket and particularly to the nuances of the five-day game. He admits India were sub-standard in England three years ago but says that injuries to important players and a feel that the guard was changing contributed to that. “You cannot hide in Test cricket, England were too good for us. The challenge of Test cricket is unmatched by anything else in my experience. I love the planning and the strategy, the requirements of patience and consistency and the chance of recovery. But I get bored playing on a dead pitch. Trent Bridge was one of those, to play and to watch. Pitches should have their indigenous qualities exaggerated. Spin in India, pace and bounce in Australia, seam and swing in England. To survive in the modern era, the game needs results, not draws.” He is a fan of the 50- over game – “because it bridges the gap. Test cricket and T20 are too far apart; 50 overs is an important format”.

I ask if he has days, weeks, seasons even, when he is over it; if there are periods when he would rather be a million miles away. Surely the urge for escape must overtake his enthusiasm for game and country. “Not really. I don’t think about it much. Only injury brings on that sort of doubt or emotion. Otherwise, I live for the moment. I’m with you here and that’s good. And tomorrow will be good. Essentially I’m very content and happy with my life.

“Sakshi provides the spark I might sometimes need. Jokes aside, she is the person to whom I have reference. She is both loving and inspirational. Sometimes she travels with me but cricket is easier without having to ensure her safe passage. One day, we will see the sights together and she says that then, can she please move to number two on the list of my priorities! But for the moment I am here to play cricket. I have been given this honour to play for my country so I want see it through.”

The food is finished. Word has spread and fans are gathering at the diner’s door. We switch to short, sharp questions and equally short responses.

Will you grow your hair long again? “No.”

Why did you shave it off after the World Cup final? “It had been a stressful few months, I admit. Many people were demanding that we won the World Cup at home. The bare heard was the final goodbye to the pressure we had been under. It was like we could start afresh.”

If you could have one player from any other team, who would it be? “Any pacy, seam-bowling allrounder. You can build a team around one of those. And no, I’m not naming names!

****************************************

How long will you play for? “My job is to get the team right and then move away. I couldn’t stand down after the tour here three years ago. I don’t believe in deserting when the chips are down. And speed, I’ll know it is time to finish when I am no longer one of the fastest movers in the team. At that point I will surely know age is winning.”

Other than involvement with the military, what will happen when you retire? “I would like to build some academies, to provide facilities and opportunities for the young.”

Is it ever lonely on the road? “My door is always open, unless Sakshi is in town and then it shuts occasionally. Seriously, I always leave the door to my room open and there is a constant flow and buzz. No, I am never lonely.”

Does anything keep you awake at night? “No, I sleep well.” Oh come on MS, there must something, some anxiety? “Well, maybe if I am going shooting the next day, to the pistol range say, then the excitement has me tossing and turning a bit.” Ye gods.

Will you have children? “I hope so! We are practising!!” And what will you expect of them? “A sense of fun, some discipline, to treat others as they would treat themselves.” Sport? “If they would like. They will be raised with an outdoor life, running free and happy I hope.”

Is he planning to stay at home with Mr and Mrs D? “Why not? Our culture is different to yours. It is more European. We look after our parents when they get old as they looked after us when we were young.”

He gets up from the table to pay the bill. The waiters line up for selfies. Outside, the night is closing in but still people want their piece of history with the most marketable, perhaps most remarkable, cricketer on the planet. As we walk, I ask if he thinks captaincy is a calling. “Well, I remember playing in a senior district match, long before I ever captained a team. I was bowling and one of the team dropped a catch and then, with a couple of others, laughed about it like they couldn’t care less. Between overs I told them to stand together, well out of the way, and laugh all they like. I said the rest of us will get on with taking the wickets.

“I suppose that suggests I wanted to be the leader. Time allows you to learn to read the game and then you work out the direction you think a team should take. After that, you decide on the process and if you get the job, you put it in place. So we are back with the process, where we began two hours ago.”

Before we say goodnight, he leaves me with a simple final thought. “A perfect smile is too often missing from the faces of the world. If cricket can provide more smiles, it is doing its job.” He said we should meet again and that he would tell me more about his friends in the special forces. I can’t wait.

This interview featured in issue 119 of All Out Cricket magazine.

Source : http://www.alloutcricket.com/cricket/features/aoc-exclusive-ms-dhoni-interview

Disable an index in postgres

If you have got one index on a table that you would like to temporarily disable you can do following steps.

You can poke the system catalogue to disable an index:

update pg_index set indisvalid = false where indexrelid = 'test_pkey'::regclass

This means that the index won’t be used for queries but will still be updated. It’s one of the flags used for concurrent index building.

If you want to run a single query without using index, you can use following query.

begin;
drop index mytable_index;

explain analyze select * from mytable;
rollback;

I don’t think there is a way to disable just one, though you can do this in a transaction to make recovering from it very simple. You can also disable indexscan to disable all indices.

Reference : http://stackoverflow.com/questions/6146024/is-it-possible-to-temporarily-disable-an-index-in-postgres

‘ascii’ codec can’t encode characters : ordinal not in range(128)

I work on a system which fetches data from a database (Postgres) using python & writes them into a csv file. Few days ago i came through an error which was showing “ascii codec can’t encode characters : ordinal not in range(128)” whenever i tried to fetch data from a particular date range.

Problem : 

When ever i tried to fetch data of a particular data range (suppose date range between 1-Jan-2013 to 31-Jan-2013) error popped up. although data fetching between other data range was working fine. That clearly meant that problem was in data of that date range only. Actually problem was in few entry of one of the column(s) (email) of my table, which was storing some Non-ASCII value . When python tried to write that value into the csv, it couldn’t process the value & error came up.

Solution :

Either you can make that value (data) unicode in python by  using:

email = [DefectedValue].encode('utf-8').strip()

Or you can find the data actually causing problems and remove them. for finding the defected data you can run following command in your PostgreSQL terminal.

SELECT * FROM YourTableName WHERE EffectedColumn similar to '%[^\x20-\x7e]+%' ;

You can update/delete that tuple which is storing Non-ASCII characters.

Make primary key with two or more field in Django

Most of the time, people don’t actually need their composite (multi-column) key to be the primary key.

Django operates best with surrogate keys –  that is, it automatically defines an autoincrement field called id and sets that to be the primary key. That is suitable for almost all the users.

If you then need to enforce a unique constraint on your model across two or more fields, you can use the unique_together setting in the inner Meta class.

class Student(models.Model):
    Name = models.CharField(max_length=100)
    Roll = models.CharField(max_length=20)
    Department = models.CharField(max_length=15)

    class Meta:
        unique_together = (('Roll', 'Department'),)

It would act as a surrogate primary key column.

Sets of field names that, taken together, must be unique:

unique_together = (('Roll', 'Department'),)

This is a tuple of tuples that must be unique when considered together. It’s used in the Django admin and is enforced at the database level (i.e., the appropriate UNIQUE statements are included in the CREATE TABLE statement).

For convenience, unique_together can be a single tuple when dealing with a single set of fields :

unique_together = ('Roll', 'Department')

Python Decorators

Python Conquers The Universe

In August 2009, I wrote a post titled Introduction to Python Decorators. It was an attempt to explain Python decorators in a way that I (and I hoped, others) could grok.

Recently I had occasion to re-read that post. It wasn’t a pleasant experience ? it was pretty clear to me that the attempt had failed.

That failure ? and two other things ? have prompted me to try again.

  • Matt Harrison has published an excellent e-book Guide to: Learning Python Decorators.
  • I now have a theory about why most explanations of decorators (mine included) fail, and some ideas about how better to structure an introduction to decorators.

There is an old saying to the effect that “Every stick has two ends, one by which it may be picked up, and one by which it may not.” I believe that most explanations of decorators fail because they pick…

View original post 1,457 more words

Count and group by equivalent in Django Queryset

While working on a Django project, I came through a problem. I wanted to select  a column & count of that column group by that column from a table. (categoryname & count  of items for each category), so in simple SQL it would be as simple as this:

SELECT column_name , COUNT(column_name) FROM TableName GROUP BY column_name;

Now Django Sub Query for the following given SQL code would be.


data = MyModel.objects.filter(column_1_gt = 'some value').values('column_name').order_by().annotate(Count('column_name'))

  • values(‘column_name) –  for inclusion only player_type field into GROUP BY clause.
  • order_by() – for exclusion possible default ordering that can cause not needed fields inclusion in SELECT and GROUP BY.

 

PostgreSQL Indexes

There are many types of indexes in Postgres, as well as different ways to use them. In this article we give an overview of the types of indexes available, and explain different ways of using and maintaining the most common index type: B-Trees.

An index is a way to efficiently retrieve a relatively small number of rows from a table. It is only useful if the number of rows to be retrieved from a table is relatively small (i.e. the condition for retrieving rows – the WHERE clause – is selective). B-Tree indexes are also useful for avoiding sorting.

Index Types

Postgres supports many different index types:

  • B-Tree is the default that you get when you do CREATE INDEX. Virtually all databases will have some B-tree indexes. The B stands for Balanced, and the idea is that the amount of data on both sides of the tree is roughly the same. Therefore the number of levels that must be traversed to find rows is always in the same ballpark. B-Tree indexes can be used for equality and range queries efficiently. They can operate against all datatypes, and can also be used to retrieve NULL values. Btrees are designed to work very well with caching, even when only partially cached.
  • Hash Indexes are only useful for equality comparisons, but you pretty much never want to use them since they are not transaction safe, need to be manually rebuilt after crashes, and are not replicated to followers, so the advantage over using a B-Tree is rather small.
  • Generalized Inverted Indexes (GIN) are useful when an index must map many values to one row, whereas B-Tree indexes are optimized for when a row has a single key value. GINs are good for indexing array values as well as for implementing full-text search.
  • Generalized Search Tree (GiST) indexes allow you to build general balanced tree structures, and can be used for operations beyond equality and range comparisons. They are used to index the geometric data types, as well as full-text search.

Why is my query not using an index?

There are many reasons why the Postgres planner may choose to not use an index. Most of the time, the planner chooses correctly, even if it isn’t obvious why. It’s okay if the same query uses an index scan on some occasions but not others. The number of rows retrieved from the table may vary based on the particular constant values the query retrieves. So, for example, it might be correct for the query planner to use an index for the query select * from foo where bar = 1, and yet not use one for the query select * from foo where bar = 2, if there happened to be far more rows with “bar” values of 2. When this happens, a sequential scan is actually most likely much faster than an index scan, so the query planner has in fact correctly judged that the cost of performing the query that way is lower.

Partial Indexes

A partial index covers just a subset of a table’s data. It is an index with a WHERE clause. The idea is to increase the efficiency of the index by reducing its size. A smaller index takes less storage, is easier to maintain, and is faster to scan.

For example, suppose you allow users to flag comments on your site, which in turn sets the flagged boolean to true. You then process flagged comments in batches. You may want to create an index like so:

CREATE INDEX articles_flagged_created_at_index ON articles(created_at) WHERE flagged IS TRUE;

This index will remain fairly small, and can also be used along other indexes on the more complex queries that may require it.

Expression Indexes

Expression indexes are useful for queries that match on some function or modification of your data. Postgres allows you to index the result of that function so that searches become as efficient as searching by raw data values. For example, you may require users to store their email addresses for signing in, but you want case insensitive authentication. In that case it’s possible to store the email address as is, but do searches on WHERE lower(email) = ''. The only way to use an index in such a query is with an expression index like so:

CREATE INDEX users_lower_email ON users(lower(email));

Another common example is for finding rows for a given date, where we’ve stored timestamps in a datetime field but want to find them by a date casted value. An index like CREATE INDEX articles_day ON articles ( date(published_at) ) can be used by a query containing WHERE date(articles.created_at) = date('2011-03-07').

Unique Indexes

A unique index guarantees that the table won’t have more than one row with the same value. It’s advantageous to create unique indexes for two reasons: data integrity and performance. Lookups on a unique index are generally very fast.

In terms of data integrity, using a validates_uniqueness_of validation on an ActiveModel class does not really guarantee uniqueness because there can and will be concurrent users creating invalid records. Therefore you should always create the constraint at the database level – either with an index or a unique constraint.

There is little distinction between unique indexes and unique constraints. Unique indexes can be though of as lower level, since expression indexes and partial indexes cannot be created as unique constraints. Even partial unique indexes on expressions are possible.

Multi-column Indexes

While Postgres has the ability to create multi-column indexes, it’s important to understand when it makes sense to do so. The Postgres query planner has the ability to combine and use multiple single-column indexes in a multi-column query by performing a bitmap index scan. In general, you can create an index on every column that covers query conditions and in most cases Postgres will use them, so make sure to benchmark and justify the creation of a multi-column index before you create them. As always, indexes come with a cost, and multi-column indexes can only optimize the queries that reference the columns in the index in the same order, while multiple single column indexes provide performance improvements to a larger number of queries.

However there are cases where a multi-column index clearly makes sense. An index on columns (a, b) can be used by queries containing WHERE a = x AND b = y, or queries using WHERE a = x only, but will not be used by a query using WHERE b = y. So if this matches the query patterns of your application, the multi-column index approach is worth considering. Also note that in this case creating an index on a alone would be redundant.

B-Trees and sorting

B-Tree index entries are sorted in ascending order by default. In some cases it makes sense to supply a different sort order for an index. Take the case when you’re showing a paginated list of articles, sorted by most recent published first. We may have a published_at column on our articles table. For unpublished articles, the published_at value is NULL.

In this case we can create an index like so:

CREATE INDEX articles_published_at_index ON articles(published_at DESC NULLS LAST);

In Postgres 9.2 and above, it’s of note that indexes are not always required to go to the table, provided we can get everything needed from the index (i.e. no unindexed columns are of interest). This feature is called “Index-only scans”.

Since we will be querying the table in sorted order by published_at and limiting the result, we may get some benefit out of creating an index in the same order. Postgres will find the rows it needs from the index in the correct order, and then go to the data blocks to retrieve the data. If the index wasn’t sorted, there’s a good chance that Postgres would read the data blocks sequentially and sort the results.

This technique is mostly relevant with single column indexes when you require “nulls to sort last” behavior, because otherwise the order is already available since an index can be scanned in any direction. It becomes even more relevant when used against a multi-column index when a query requests a mixed sort order, like a ASC, b DESC.

Managing and Maintaining indexes

Indexes in Postgres do not hold all row data. Even when an index is used in a query and matching rows where found, Postgres will go to disk to fetch the row data. Additionally, row visibility information (discussed in the MVCC article) is not stored on the index either, therefore Postgres must also go to disk to fetch that information.

Having that in mind, you can see how in some cases using an index doesn’t really make sense. An index must be selective enough to reduce the number of disk lookups for it to be worth it. For example, a primary key lookup with a big enough table makes good use of an index: instead of sequentially scanning the table matching the query conditions, Postgres is able to find the targeted rows in an index, and then fetch them from disk selectively. For very small tables, for example a cities lookup table, an index may be undesirable, even if you search by city name. In that case, Postgres may decide to ignore the index in favor of a sequential scan. Postgres will decide to perform a sequential scan on any query that will hit a significant portion of a table. If you do have an index on that column, it will be a dead index that’s never used – and indexes are not free: they come at a cost in terms of storage and maintenance.

When tuning a query and understanding what indexes make the most sense, never try to it on your development machine. Whether an index is used or not depends on a number of factors, including the Postgres server configuration, the data in the table, the index and the query. For instance, trying to make a query use an index on your development machine with a small subset of “test data” will be frustrating: Postgres will determine that the data set is so small that it’s not worth the overhead of reading through the index and then fetching the data from disk. Random I/O is much slower than sequential, so the cost of a sequential scan is lower than that of the random I/O introduced by reading the index and selectively finding the data on disk. Performing index tuning should be done on production, or on a staging environment that is as close to production as possible.

When you are ready to apply an index on your production database, keep in mind that creating an index locks the table against writes. For big tables that can mean your site is down for hours. Fortunately Postgres allows you to CREATE INDEX CONCURRENTLY, which will take much longer to build, but does not require a lock that blocks writes. Ordinary CREATE INDEX commands require a lock that blocks writes but not reads.

Finally, indexes will become fragmented and unoptimized after some time, especially if the rows in the table are often updated or deleted. In those cases it may be required to perform a REINDEX leaving you with a balanced and optimized index. However be cautious about reindexing big indexes as write locks are obtained on the parent table. One strategy to achieve the same result on a live site is to build an index concurrently on the same table and columns but with a different name, and then dropping the original index and renaming the new one. This procedure, while much longer, won’t require any long running locks on the live tables.

Postgres provides a lot of flexibility when it comes to creating B-tree indexes that are optimized to your specific use cases, as well as options for managing the ever-growing database behind your applications. These tips should help you keep your database healthy, and your queries snappy.

Source : https://devcenter.heroku.com/articles/postgresql-indexes