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 but Oracle needs additional considerations.
Read More
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 I had to hint the Oracle example to get the same because the Oracle optimizer chooses the index scan over the table scan in that case. Here is where I learned a big difference between Postgres and Oracle. They…
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 documentation for the query planner: seq_page_cost (floating point) Sets the planner’s estimate of the cost of a disk page fetch that is part of a series of sequential fetches. The default is 1.0.
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 that I take some behavior as granted for any SQL databases. I recently posted a tweet about one of them, comparing latest Postgres version to earliest Oracle version I have on my laptop. The goal of the tweet was…
By Franck Pachot . This is the start of a series on PostgreSQL execution plans, access path, join methods, hints and execution statistics. The approach will compare Postgres and Oracle. It is not a comparison to see which one is better, but rather to see what is similar and where the approaches diverge. I have a long experience of reading Oracle execution plans and no experience at all on Postgres. This is my way to…
By Franck Pachot . I run my laptop with Windows 10 for office programs, and VirtualBox machines with Linux for the big stuff (Oracle databases). I have also Cygwin installed on Windows for GNU programs. I wanted to quickly install PosgreSQL and rather than installing it in a Linux VM, or as a Windows program, I installed the Cygwin version of it. Here is how.
By Franck Pachot . In the previous post we have seen the Index Scan, where a where clause condition uses the index structure to find rows and then fetches remaining columns from the table. The case was an optimal one where the correlation/clustering factor was good. Now I’ll do the opposite: the worst clustering factor.