PGCon2016 - 20180510

PGCon 2016
The PostgreSQL Conference

Dilip Kumar
Day Talks - Day 1 - 2016-05-19
Room DMS 1110
Start time 15:00
Duration 00:45
ID 936
Event type Lecture
Track Performance
Language used for presentation English

Run Simple Query Faster

When we consider the any executor engine, its very generic and designed for supporting wide range of queries and so does the postgres SQL engine. Consider very simple query i.e TPC benchmark or PGBench, where the queries and only simple update, select, insert etc., Here simple queries means queries which don't have complex quals, join, sub-queries, some function calls in quals.

We have wrote a simple contrib module which have executor engine for simple queries, and attached to postgreSQL Executor Hook.

Its shows ~30% improvement on Update and ~15-20% on insert and select and overall 20% improvement on PgBench on Prepared Queries, and ~8% on Non-Prepared queries.

Idea Details: 1. Attach a simple executor Hook to postgres Executor engine and when it goes to Executor start Hook check whether query is simple or not by processing the qual and various parameter, most of the complex queries will be rejected in very early check so it will not be a overhead for non simple queries (like join will have 2 range table entries so it will be rejected).

  1. If it is a simple plan, then mark this plan as simple, store this in statement also so that in case of prepared statement, next time no need to check again.

  2. In Case of prepared statement other then just avoiding generic infrastructure we can save many processing, i.e. Estate, Target List initialization, Qual initialization, tuple descriptor creation mostly these will be small amount of memory and can save huge cost.

  3. Apart from these simple queries we have expanded this are to cover some of the complex queries, which by nature look complex, but by analyzing their quals and other info it proves to be a simple queries and can be executed as simple nested for loop instead of generic join infrastructure.