Using pg_variables as temporary tables
PostgreSQL provides possibility to create temporary tables. Though a temporary table is accessible only to a single session and is removed at the end of the session, all information about it is stored in the system catalogs of PostgreSQL. This is related to several issues, which make it difficult or impossible to use temporary tables in some cases. There are attempts to solve this feature, including in our company. But they have not yet succeeded, mainly because of the PostgreSQL engine. In the talk I want to tell about simple and small pg_variables extension. It allows you to create table variables along with scalar ones. I will tell how it can replace temporary tables, what advantages and disadvantages it has.
Слайды
Видео
Другие доклады
-
45 мин
Darafei Praliaskouski JunoPostGIS for Disaster Management
PostGIS is spatial extension for PostgreSQL.
This talk will go in depth on using PostGIS for disaster management: which functions can be used and for what.
-
45 мин
Jignesh Shah Amazon Web ServicesTips and Tricks with Amazon RDS for PostgreSQL
Managed database services are gaining in popularity. In this session we look at how best to configure Amazon RDS for PostgreSQL and also look at common user operations of using RDS for PostgreSQL. We will also look beyond common user operations and into some specific optimizations related to upgrade, logical replication, performance, and reducing downtime.
-
45 мин
Aleksander Kuzmenkov Postgres ProfessionalTowards more efficient query plans: PostgreSQL 11 and beyond
A major responsibility of a database engine is to convert a declarative SQL query to an efficient execution plan, employing various methods to scan and join the relations. There is always a development effort to improve this area. What clever execution plans can PostgreSQL generate, what's new in version 11 and what is in development? To name a few things, the joins are optimized by removing unneeded outer and inner joins, and reducing joins from outer and semi to inner. There is work to enable merge joins on inequality and range overlap, and to improve join selectivity estimates with multi-column statistics. When it comes to scanning a single relation, covering indexes allow to use index-only scans more often. Incremental sort and more precise estimation of sorting costs help generate better paths when sorted output is required, e.g. when using GROUP BY and ORDER BY or performing merge joins. This talk aims to give an overview of such optimizations that already exist and that are being developed now.
-
22 мин
Stepan Danilov РТ ЛабсOptimization of optimized and not quite
I would like to share my experience in optimization queries in PostgreSQL of RMIS (Regional Medical Information System).