PostgreSQL
Performance improvements and new use scenarios for SPGiST access method
In the PostgreSQL core, we have the SPGiST index, which is more lightweight and significantly faster than GiST for the supported data types. In many cases, it is the most suitable option for geometric data like points, boxes, etc. An easy way to increase the selection speed is to eliminate the need for the results recheck by a table. But with the index being single-column by design we could not enjoy index-only scans for multicolumn queries.
The talk covers my improvement of the SPGiST access method. Non-key columns can now be included in a single key-column index in a way, similar to that have in GiST and B-tree. This adds the ability to make fast index-only scans for multicolumn queries. Also, non-key columns with data types without SPGiST opclass can be added, which creates an opportunity for the new use cases. In my presentation, I will justify the modification, discuss new use cases of GiST and SPGiST indexes, and share some performance benchmarks.
Additional information
| Type | devroom |
|---|
More sessions
| 2/6/21 |
FOSDEM would not be FOSDEM without waffles... What if we coud use Postgres to make waffles ? During this talk we will use the excuse of FOSDEM and Brussels to create an extension that will look for the best waffle recipe and use Postgres to display it. During this journey, on top of making delicious waffles, we will : - understand what an extension is - find the steps needed to create an extension - make this work all together - install our extension - display the best Waffle recipe
|
| 2/6/21 |
In order to connect to external databases, PostgreSQL supports Foreign Data Wrappers (FDW), and there are already many FDWs. However, among of FDWs have various restrictions preventing utilization of external databases features. As such a restriction, FDWs for schemaless databases need to change the foreign table definition, when some columns are added in remote database. This restriction can not take full advantage of the schemaless feature. In this time, we considered implementing FDW that ...
|
| 2/6/21 |
Performing schema changes on a live system is challenging because it often involves high level of locks leading to downtime. It becomes harder and harder when the number of databases to manage raise continuously. At OVHcloud, a major cloud computing provider in Europe, we used to manage such changes approximately. With our startup mindset, we used to yell "Who can ALTER my database?" and hope for someone sitting next to you to answer. Database engineers were used to copy paste bunch of SQL ...
|
| 2/6/21 |
Synchronous or quorum replication is the basis of the modern HA cluster. At Yandex.Cloud we are using replication-based HA PostgreSQL clusters for many years. In this talk, we want to introduce basic concepts and highlight caveats that seem important to us.
|
| 2/6/21 |
In this talk we want to present how Microsoft team composed of people from two different teams approached the project and solved the migration issues using ora2pg and was able to prove that Postgres Single Server can perform equally well as Oracle Exadata. We will present our ways of working and also some main technical challenges that we faced including migration of BULK COLLECT’s, hierarchical queries, refcursors and others more complicated Oracle constructs.
|
| 2/6/21 |
Over many years of PostgreSQL development I have collected a lot of information about setting up a development environment and assorted notes about the development process that would be worth sharing. If you are new to PostgreSQL development, this can help you get set up. If you are an experienced developer, we can compare notes.
|
| 2/6/21 |
Query optimizer is one of the key components which determines DBMS performance under OLAP workload. Nevertheless, it was shown that query optimizer often fails to find a good execution plan because of incorrect cardinality estimations. The perspective approach to improve cardinality estimation quality is adaptive query optimization. In contrast with classical approaches, which rely on the precomputed histograms, it utilizes the execution statistics of the previously executed queries to refine ...
|
