Build a PostgreSQL Docker image with pg_hint_plan and pg_stat_statements

With more than 20 years as a database consultant, I'm 100% convinced that any database, lab, dev, or prod, should have the tools to troubleshoot and workaround problems, already installed, ready to use. For PostgreSQL this means two extensions: pg_stat_statement and pg_hint_plan. I will never understand why pg_hint_plan is not there by default in all installations. Often, I reproduce the problems on a lab and here is how to build a docker image with both installed.

Here is how I build it from PostgreSQL version 14 image:

cat > Dockerfile <<'DOCKERFILE'
# install pg_hint_plan from rpm
FROM docker.io/postgres:14
ADD https://github.com/ossc-db/pg_hint_plan/releases/download/REL14_1_4_0/pg_hint_plan14-1.4-1.el8.x86_64.rpm .
RUN  apt-get update -y ; apt-get install -y alien wget ; alien ./pg_hint_plan*.rpm ; dpkg -i pg-hint-plan*.deb
# copy the minimal files to a postgres image
FROM docker.io/postgres:14
COPY --from=0 /usr/pgsql-14/share/extension/pg_hint_plan.control /usr/share/postgresql/14/extension
COPY --from=0 /usr/pgsql-14/share/extension/pg_hint_plan--1.4.sql /usr/share/postgresql/14/extension
COPY --from=0 /usr/pgsql-14/lib/pg_hint_plan.so /usr/pgsql-14/lib/pg_hint_plan.so /usr/lib/postgresql/14/lib
ENV  PGPASSWORD=postgres
CMD ["postgres","-c","shared_preload_libraries=pg_hint_plan,pg_stat_statements"]
DOCKERFILE

docker build -t pachot/pg_hint_plan --platform=linux/amd64 .
docker push pachot/pg_hint_plan 

And test it:

docker run -d --name pg -p 5432:5432 -e POSTGRES_PASSWORD=postgres pachot/pg_hint_plan 
sleep 5
docker exec -i pg psql -U postgres -e -c "
create extension if not exists pg_hint_plan;
create extension if not exists pg_stat_statements;
create temporary table demo (a int primary key);
" -c "
explain select /*+ Leading((a b)) */ * from demo a join demo b using(a)
" -c "
explain select /*+ Leading((b a)) */ * from demo a join demo b using(a)
" -c "
select calls, query from pg_stat_statements;
"

The output shows that my join order follows what I've set with the Leading hint, and that all statements executions were recorded:

This is very helpful to troubleshoot and understand the choices of the query planner. For example, here, I can see that the cost of both join order are the same and one was just picked up with no costing reason.

And to troubleshoot the troubleshooting, you can add more traces like:

docker exec -i pg psql -U postgres -e -c "
create temporary table demo (a int primary key);
set pg_hint_plan.debug_print=verbose;
set client_min_messages = log;
" -c "
explain select /*+ 
Leading((b a)) SeqScan(a) IndexScan(b) NestLoop(a b) 
SeqScan(x) IndexScan(a) Set(x=2) GoFaster(b)  */ 
* from demo a join demo b using(a)
"

With set pg_hint_plan.debug_print=verbose and set client_min_messages = log the following is displayed to understand which hints were used: