Execution plan

RCSI with foreign keys, NULL values and paramater sniffing behavior

In this blog post let’s go back to the roots (DBA concern) with a discussion with one of my friends about a weird transaction locking issue. In fact, this discussion was specifically around two questions. The(…)

Postgres vs. Oracle access paths XI – Sample Scan

By Franck Pachot . I was going to end this series with the previous post because the last access path available in Postgres is a bit special: a Seq Scan that returns only a sample of(…)

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(…)