PGCon2016 - 20180510
PGCon 2016
The PostgreSQL Conference
| Speakers | |
|---|---|
|
Dilip Kumar |
| Schedule | |
|---|---|
| Day | Talks - Day 1 - 2016-05-19 |
| Room | DMS 1110 |
| Start time | 15:00 |
| Duration | 00:45 |
| Info | |
| 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).
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.
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.
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.