Speaker(s): Maksym Boguk (Australia)

Abstract:
Unconventional query optimization in PostgreSQL, or how to teach an elephant to dance rock'n'roll.Query performance is usually tuned by simply adding indexes and rewriting the query minimally, while the basic idea of the query stays the same. For many query, this is not the best way to optimize them: you need to rethink and rewrite them completely. To do this, you need to know how optimizer and executor work, and keep that in mind while composing your queries. In this talk, I will present an overview of a few less known and rarely used techniques such as:* using and implementing loose index scan for different scenarios;* using IOS in situation where it isn't applicable directly;* replacing IN (long list of values) with JOIN VALUES ();* moving a limit or offset clause inside joins;* and other tricks with row types and arrays of row types.Using these techniques and some other ideas, we will learn how to optimize a 'friends feed/subscription news' query, which is a typical task in many projects. We will diretcly compare performance of four different approaches: direct approach (simplest query), optimized direct approach (more complicated version of it), pl/pgsql with different algorithms, and implementation of this pl/plsql code in plain sql. I will show how this approach can convert almost any read-only non-dynamic (no EXECUTE statements and no exceptions) pl/pgsql code to a plain sql query.

(Type: | Track: Database - PGDay | Room: Curie & Hershel (Floor 2))

Event Page: http://2017.fossasia.org

Produced by Engineers.SG