Community

Blog Events Webinars Tutorials Forum
  1. Blog
  2. Events
  3. Webinars
  4. Tutorials
  5. Forum
Create Account
×
Community Blog What Could Be the Factor Leading to the Slow Execution of the First SQL Statement?

What Could Be the Factor Leading to the Slow Execution of the First SQL Statement?

The article introduces the factor contributing to the slow execution of the first SQL statement related to PostGIS library loading in PostgreSQL.

By digoal

Background

Having nothing to do with caching, what could be the factor contributing to the slow execution of the first SQL statement?

PostgreSQL supports plug-ins. A plug-in usually contains a library file. You need to load the library file when you use the features of the plug-in, such as types, functions, operators, and indexes. When is the library file loaded?

1. Automatic loading:

# - Shared Library Preloading -
  
#local_preload_libraries = ''  The common user can modify this. The library file is placed in $libdir/plugins/... and is loaded upon connection.
#session_preload_libraries = ''  The superuser can modify this. The library file is placed in $libdir/... and is loaded upon connection.
#shared_preload_libraries = ''  # (change requires restart)  The library file is loaded when the database is started.

Parameter details: https://www.postgresql.org/docs/16/runtime-config-client.html

The difference test between session_preload_libraries and local_preload_libraries is as follows:

create role r1 login;
  
root@5fd24287ff29:/var/lib/postgresql/14/pgdata# PGOPTIONS="-c session_preload_libraries=postgis-3" psql -U r1
psql: error: connection to server on socket "/var/lib/postgresql/14/pgdata/.s.PGSQL.1921" failed: FATAL:  permission denied to set parameter "session_preload_libraries"
  
root@5fd24287ff29:/var/lib/postgresql/14/pgdata# PGOPTIONS="-c local_preload_libraries=postgis-3" psql -U r1
psql: error: connection to server on socket "/var/lib/postgresql/14/pgdata/.s.PGSQL.1921" failed: FATAL:  could not access file "$libdir/plugins/postgis-3": No such file or directory
  
  
root@5fd24287ff29:/usr/lib/postgresql/14/lib/plugins# ln -s ../postgis-3.so ./
root@5fd24287ff29:/usr/lib/postgresql/14/lib/plugins# ll
total 12K
lrwxrwxrwx 1 root root   17 Sep 18  2023 pre_prepare.so -> ../pre_prepare.so
drwxr-xr-x 1 root root 4.0K Jun 25 07:50 ..
lrwxrwxrwx 1 root root   15 Jun 26 04:10 postgis-3.so -> ../postgis-3.so
drwxr-xr-x 1 root root 4.0K Jun 26 04:10 .
  
  
root@5fd24287ff29:/usr/lib/postgresql/14/lib/plugins# PGOPTIONS="-c local_preload_libraries=postgis-3" psql -U r1
psql (14.12 (Debian 14.12-1.pgdg110+1))
Type "help" for help.
  
postgres=>

2. Manual loading through the LOAD command:

postgres=# load 'postgis-3.so';
LOAD

What Could Be the Factor Contributing to the Slow Execution of the First SQL Statement?

As the library file needs to be loaded when using the plug-in, the first query that involves loading the library may be slow.

Next, I will use PostGIS as an example to demonstrate the overhead of loading libraries.

Write test data.

postgres=# create extension postgis;
CREATE EXTENSION
postgres=# \dx
                                List of installed extensions
Name   | Version |   Schema   |                        Description
---------+---------+------------+------------------------------------------------------------
plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
postgis | 3.4.2   | public     | PostGIS geometry and geography spatial types and functions
(2 rows)
  
postgres=# create table t (id int, pos geometry);
CREATE TABLE
postgres=# insert into t select generate_series(1,1000), st_makepoint(120+random()*10, 30+random()*10);
INSERT 0 1000

When the PostGIS library is used for the first time in a session, it takes 21 milliseconds.

root@5fd24287ff29:/var/lib/postgresql/14/pgdata# psql
psql (14.12 (Debian 14.12-1.pgdg110+1))
Type "help" for help.
  
postgres=# explain (analyze,timing,verbose) select *,st_astext(pos) from t where id=1;
                                             QUERY PLAN                                               
----------------------------------------------------------------------------------------------------  
Seq Scan on public.t  (cost=0.00..22.12 rows=1 width=68) (actual time=0.366..0.462 rows=1 loops=1)
   Output: id, pos, st_astext(pos)
   Filter: (t.id = 1)
   Rows Removed by Filter: 999
 Planning Time: 2.224 ms
 Execution Time: 21.014 ms
(6 rows)

The second time, it immediately dropped to 0.2 milliseconds.

postgres=# explain (analyze,timing,verbose) select *,st_astext(pos) from t where id=1;
                                             QUERY PLAN                                               
----------------------------------------------------------------------------------------------------  
Seq Scan on public.t  (cost=0.00..22.12 rows=1 width=68) (actual time=0.037..0.150 rows=1 loops=1)
   Output: id, pos, st_astext(pos)
   Filter: (t.id = 1)
   Rows Removed by Filter: 999
 Planning Time: 0.110 ms
 Execution Time: 0.248 ms
(6 rows)

To this end, we have enabled automatic loading of the PostGIS library at the time of user connection, which has immediately dropped the time taken for the first use from over 20 milliseconds to just 1.1 milliseconds.

root@5fd24287ff29:/var/lib/postgresql/14/pgdata# PGOPTIONS="-c session_preload_libraries=postgis-3" psql
psql (14.12 (Debian 14.12-1.pgdg110+1))
Type "help" for help.
  
postgres=# explain (analyze,timing,verbose) select *,st_astext(pos) from t where id=1;
                                             QUERY PLAN                                               
----------------------------------------------------------------------------------------------------  
Seq Scan on public.t  (cost=0.00..22.12 rows=1 width=68) (actual time=0.434..0.644 rows=1 loops=1)
   Output: id, pos, st_astext(pos)
   Filter: (t.id = 1)
   Rows Removed by Filter: 999
 Planning Time: 2.287 ms
 Execution Time: 1.160 ms
(6 rows)

When used in conjunction with a connection pool, the effect is even better because the connections from the connection pool software to the database are reused. Once a connection is established, the dynamic library is loaded, and any operations involving the dynamic library through the connection pool's connections are very fast.

PostgreSQL Database Developers POLARDB Preload libraries
0 1 0
Share on

Read previous post:

How to Remove Duplicate and Similar Contents in Vector Databases

Read next post:

Tips for Executing SQL Statements in Batch: Which Is Better, gexec or Dynamic SQL?

digoal

286 posts | 25 followers

You may also like

Comments

digoal

286 posts | 25 followers

Related Products

More Posts by digoal

See All