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; "
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) "
set pg_hint_plan.debug_print=verbose and
set client_min_messages = log the following is displayed to understand which hints were used: