How to get things done Part1

Something bizarre happened today. I asked something to be done, and it actually got done quickly! Now you are probably asking why is this bizarre?

In big firms, there is a lot of bureaucracy to get something done. The change I wanted was small, but it could have other ramifications. Most of the people I spoke to said I'd have pretty small chance of getting it done soon.
I put down the quick response to a few things, which I'll share with you. It seems obvious, yet you can easily forget any of these.

1. Find the exact person to talk to: In most firms, there is some highly complex 'ticketing' system when you request a change. If you don't fill in the fields correctly, your service ticket will bounce between people, all of whom don't really want to look after your problem. Often your co-workers will know who was the person who looked after similar problems before. So the best thing to do is email that person and then state your problem and ask if you need to raise a ticket. The ticketing system is still good, since it means you can priotise work, and the person answering it gets recognition, but by asking someone directly, it means you can write the ticket to go to the correct person first go.

2. State your request clearly. If I don't see something numbered and easy to do, chances are I won't do it. So don't say things in a roundbout way, but just say, I want A, B, C done by preferably Y at the latest. This basically means it'll be done by 5mins to Y, but better than two weeks after Y.

3. Allay any fears: People are scared that when they do something for you, that they'll be creating more problems for themselves later. Make it obvious that its a simple one off thing. You're not lying, you're just being optimistic...

5. Follow-up. Everyone loves recognition for doing a job well, but people are lazy giving recognition out. In bars, we tip, partly to recognise and appreciate their work, but more realistically its because we want to given just as good service next time.
In firms we can't do that, so we do the next best thing. Tell that person's supervisor that your needs were helped by their help.

Why SQL Performance Tuning is 'teh suck'.

At work of late I've been doing performance tuning in SQL. In postgresql to be precise. A friend of mine said that its great and you learn a lot. I'd say the second part is true, but the first is wrong. SQL Performance tuning, especially in postgresql is truly an abysmal process!

Part of the problem is that you have to work with a poor mans toolkit. In postgresql, your best friend for tuning your queries is explain. And thats it.
There is a visual version, but for the really complex queries (which are usually the ones that NEED the tuning in the first place), this visual version will choke and die as it requires just too much power to generate an visual display of these often frightening queries.

Explain basically tells you what the database 'planner' is probably going to do to answer your query. I.e, what tables it will hit, what merging, connecting, sorting etc it will do to answer your demands. This planner is quite intelligent in most cases, except with complex databases like the one I'm working on. This is where you have to dabble in the black arts. Eye of newt, and key of foreign table style.

Things that help the planner

One thing that really helps the planner is making your foreign keys explict in joins. So sometimes if a table has a foreign key back and forth, just mention them both and it can often improve things dramatically.

Beware of joining views which have unions defined in them. Unions seem to create temporary tables which means bye bye indexing. Which leads me to the next point. When it comes to searching, indexes are king. It can vastly improve the speed of your joins if you use indexes, so never sacrifice (or forget indexes). Sequential scans are very costly, but index scans can be much faster. But do be aware, that when the table is too small, indexes won't be used (seq scans are faster), and when the table is too large, sometimes it won't use indexes either!

Avoid using tables which you know are going to be huge, but which you can avoid. Postgresql is particularly relevant here. Since it allows using inheritance, your smaller specialised 'sub tables' could be more useful for the join than the big table since it can be scanned faster. It also be fine to scan each of those sub tables separately and union them at the end before joining (because you'd have got smaller filtered sets to work with).

Relative sizes also matter. Say if you are joining Table A to Table B to Table C, and you know A and B are generally smaller than C. Its a lot faster to join A and B first, and then join the results of this to C. Rather than joining all at the same time.

I'm working in Postgresql 7.3 and granted this is getting a bit old. 8.0+ has apparently made a lot of improvements and hence half the work I've done is probably no longer necessary. Which begs the question, why aren't we using pgsql 8.0. Well dear reader, thats a question for another day :)

You know what blogger really needs? Categories!
This blog was called Technica obscura and I don't think there is anything technical in it at the moment. And I'd like to put some techy stuff for some readers who are into learning new things and also as a way of storing things I've learnt. But of course I also got to look after my regulars who like to find out about the wonderful dreary mess that is my life.

So I shall start hunting down how to put these things into categories!