Infrastructure at your Service

PostgreSQL Archives - Blog dbi services

Franck Pachot

Postgres vs. Oracle access paths IX – Tid Scan

By | Oracle, Postgres | No Comments

In the previous post we have seen how Postgres and Oracle finds the table row from the index entry. It uses the TID / ROWID. I’ll focus on this access path and I will have covered all Postgres access paths to table data. Oracle ACCESS BY ROWID I start with Oracle because we already have seen the TABLE ACCESS BY ROWID. I’ll decompose an index acces to the table. The first step is getting the…

 
Read More
Franck Pachot

Postgres vs. Oracle access paths VIII – Index Scan and Filter

By | Oracle, Postgres | 2 Comments

In the previous post we have seen a nice optimization to lower the consequences of bad correlation between the index and the table physical order: a bitmap, which may include false positives and then requires a ‘recheck’ of the condition, but with the goal to read each page only once. Now we are back to the well-clustered table where we have seen two possible access paths: IndexOnlyScan when all columns we need are in the…

 
Read More
Franck Pachot

Postgres vs. Oracle access paths IV – Order By and Index

By | Oracle, Postgres | No Comments

I realize that I’m talking about indexes in Oracle and Postgres, and didn’t mention yet the best website you can find about indexes, with concepts and examples for all RDBMS: http://use-the-index-luke.com. You will probably learn a lot about SQL design. Now let’s continue on execution plans with indexes. As we have seen two posts ago, an index can be used even with a 100% selectivity (all rows), when we don’t filter any rows. Oracle has…

 
Read More
Daniel Westermann

Developer GUI tools for PostgreSQL

By | Database Administration & Monitoring | No Comments

There was a recent thread on the PostgreSQL general mailing list asking for GUI tools for PostgreSQL. This is question we get asked often at customers so I though it might be good idea to summarize some of them in a blog post. When you know other tools than the ones listed here which look promising, let me know so I can add them. There is a list of tools in the PostgreSQL Wiki as…

 
Read More
Daniel Westermann

A wonderful PostgreSQL feature: default privileges

By | Database Administration & Monitoring | No Comments

Imagine this scenario (which is not so uncommon): You have a lot of objects in a user schema and you want to grant another user access to that tables. You can easily do this by granting select on the tables to the user and you’re fine. Really? Maybe now, but what will happen when the user which owns the objects creates new objects? Then you will need to grant those to the second user as…

 
Read More