PostgreSQL

Postgres vs. Oracle access paths X – Update

By Franck Pachot . In the previous post we have seen the cheapest way to get one row, reading only one block from its physical location. But that's the optimal case where the row has not(…)

Postgres vs. Oracle access paths IX – Tid Scan

By Franck Pachot . 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(…)

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

By Franck Pachot . 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(…)

Postgres vs. Oracle access paths VI – Index Scan

By Franck Pachot . In the previous post my queries were still reading the indexed column only, from a table which had no modifications since the last vacuum, and then didn't need to read table pages:(…)

Postgres vs. Oracle access paths V – FIRST ROWS and MIN/MAX

By Franck Pachot . We have seen how an index can help to avoid a sorting operation in the previous post. This avoids a blocking operation: the startup cost is minimal and the first rows can(…)

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

By Franck Pachot . 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(…)

Postgres vs. Oracle access paths III – Partial Index

By Franck Pachot . In the previous post I said that an Index Only Access needs to find all rows in the index. Here is a case where, with similar data, Postgres can find all rows(…)

Postgres vs. Oracle access paths II – Index Only Scan

By Franck Pachot . In the previous post I've explained a sequential scan by accident: my query needed only one column which was indexed, and I expected to read the index rather than the table. And(…)

Postgres vs. Oracle access paths I – Seq Scan

By Franck Pachot . Here is the first test I've done for my Postgres vs. Oracle access paths series and the first query did a sequential scan. It illustrates the first constant you find in the(…)

Postgres unique constraint

By Franck Pachot . I'll start a series on Postgres vs. Oracle access paths because I know Oracle and I learn Postgres. While preparing it, I came upon some surprises because I'm so used to Oracle(…)