PostgreSQL from installation to production: configuration, streaming replication, high availability with Patroni, backups with pgBackRest, performance tuning, extensions, upgrades and migrations from other database engines.
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 and then requires a ‘recheck’ of the condition, but with the goal to read each page only once. Now we are back to the…
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: it was Index Only Scan. However, we often need more columns than the ones that are in the index. Here is the Index Scan…
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 be immediately returned. This is often desired when displaying rows to the user screen. Here is more about Postgres startup cost, Oracle first_rows costing,…
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 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…
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. In the previous post I’ve executed: select sum(n) from demo1 The execution plan was: Aggregate (cost=295.29..295.30 rows=1 width=8) (actual…
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…
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…
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…
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…
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…
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. Index with good correlation /…
There have been several posts on how to load 1m rows into a database in the last days: Variations on 1M rows insert (1): bulk insert Variations on 1M rows insert(2): commit write Variations on 1M rows insert (1): bulk insert – PostgreSQL Variations on 1M rows insert(2): commit write – PostgreSQL Variations on 1M rows insert (3): TimesTen In…
