Top 60 Oracle Blogs

Recent comments


How to cancel SQL statements and disconnect sessions in #PostgreSQL 150w, https://uhesse.files.wor

How Well a Query Optimizer Handles Subqueries?

At the beginning of December, at the UKOUG Tech17 conference in Birmingham (GB), I presented a comparison of the query optimizers of MySQL 8.0.3 and PostgreSQL 10.1. One of the things I talked about is their ability to handle subqueries. I summarized my findings with the following sentence:

Simple sub-queries that are not correctly optimized were observed.

It goes without saying that such a sentence leaves a lot of questions open. After all, it is just a summary. The aim of this post is to show you which subqueries I tested, and to compare my expectations with the execution plans generated by the query optimizers. In addition, since I’m not limited in time and scope as during a 50-minute presentation, I also discuss how the Oracle Database 12.2 query optimizer handles the same queries.

Visualize PostgreSQL index file with pgdfv


In the previous blog post pgdfv (PostgreSQL data file visualizer) has been introduced. At that time the utility was able to display data file. It is now able to display index file. If you are not familiar with PostgreSQL block internals I would suggest to read Frits Hoogland study in this series of blogposts.

The utility usage is:

$ ./pgdfv
-df     Path to a datafile (mandatory if indexfile is used)
-if     Path to an indexfile
-b      Block size (default 8192)

As you can see you can now specify an indexfile. In that case the following information will be displayed:

Welcome to pgdfv: PostgreSQL data file visualizer


As you may know the PostgreSQL database page contains a lot of informations that is documented here. A great study has been done by Frits Hoogland in this series of blogposts. I strongly recommend to read Frits series before to read this blog post (unless you are familiar with PostgreSQL block internals).

By reading the contents of a page we can extract:

SystemTap for PostgreSQL Toolkit


The purpose of this post is to share some SystemTap tools that have been initially written for oracle and have been adapted for PostgreSQL.

The tools are:

  • pg_schedtimes.stp: To track time spend in various states (run, sleep, iowait, queued)
  • pg_page_faults.stp: To report the total number of page faults and splits them into Major or Minor faults as well as Read or Write access
  • pg_traffic.stp: To track the I/O (vfs, block) and Network (tcp, udp, nfs) traffic

Those tools are able to group the SystemTap probes per client connections (per database or user) and server processes.

Grouping the probes

As described into the documentation, on most platforms, PostgreSQL modifies its command title as reported by ps, so that individual server processes can readily be identified.

Postgres vs. Oracle access paths XI – Sample Scan

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 the rows, at random. However, it is the occasion to come back to the difference between random and sequential reads.

I’m still working on the same table as in the previous posts, with 10000 rows in 1429 pages. 5% of rows is 500 rows and 5% of blocks is about 72 pages.

Postgres vs. Oracle access paths X – Update

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 moved. I’ll (nearly) conclude this series about access path with an update.

Postgres vs. Oracle access paths IX – Tid Scan

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.


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 ROWID from the index entry:

SQL> select /*+ */ rowid from demo1 where n=1000;

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

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 index, and IndexScan when we select additional columns. Here is a case in the middle: the index does not have all the columns required by the select, but can eliminate all rows.

The table created is:

create table demo1 as select generate_series n , 1 a , lpad('x',1000,'x') x from generate_series(1,10000);
SELECT 10000
create unique index demo1_n on demo1(n);

Postgres vs. Oracle access paths VI – Index Scan

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 access path.