A week on the other side (part1 PostgreSQL)

For a Oracle oriented person like me, it was nice to see that indexing in PostgreSql environment has the same kind of possibility to boost performance of a query. In a OLTP environment the first star was the most meaningfull this time. Query execution time deminisched from minutes to part of a second. Talking about stars I mean the stars described in a book Relational Database Index Design and the Optimizers writen by Tapio Lahdenmäki. Recommended reading, at least the chapter DERIVING THE IDEAL INDEX FOR A SELECT. There was three separately indexed foreign key columns in a half a million rows table. All those columns were predicates in a query. All that was needed was to create a single index having all those three columns in it.

Dropping an index in postgre was not an online operation. Need to figure out why.

No comments:

Post a Comment

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.