pev2 VS pg_hint_plan

This runs in about 250ms. Let's have a look at the explain plan to understand it better. To visualise it, I am using the excellent visualisation tool from Dalibo.

The PEV2 is open source and give you a good visualization. I never used this pgmustard to compare.

https://explain.dalibo.com/

Visualize Your Plan: Visit explain.dalibo.com and paste the generated plan text and query. Then, hit Submit. The tool will generate a visualization of your query plan. Here's an example of the visualization for the fifth attempt version of the query from this post. It shows the different types of scans that were used and how the data gets combined. The duration of each operation is also shown:

You can download the whole analyzer as a simple html file and use it this way. No need to obfuscate or sanitize anything at all.

https://github.com/dalibo/pev2

You might be interested in sites like https://explain.dalibo.com/ which make the output a bit nicer to read. I use these quite often to quickly identify bottlenecks.

PostgreSQL Query Plan Analyzer and Visualizer

I didn’t know about pev2, interesting, checking it now. Did you integrate the component yourself or are you using this hosted page by them: https://explain.dalibo.com/?

IMO it‘s important to get started with indexing. Grab your most frequently used queries and run an EXPLAIN ANALYZE to identify the problems. This tool might help you to understand your execution plans. Once you identified your problems, you can build indexes and check again. Then you should regularly check if your indexes are used.

Okay so it isn't entirely clear to me, can the pg_hint_plan extension (linked in the OP) do the simple thing where we specify, for each table, which index to use?

I can't find it here

https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hin...

Because, the mssql WITH(INDEX()) is simple and intuitive. This hint table stuff seems complicated, and it's unclear to me if they can do the simple thing

pg_hint_plan —— Give PostgreSQL ability to manually force some decisions in execution plans. https://github.com/ossc-db/pg_hint_plan

I really wish the PostgreSQL core team would acknowledge that their stance on that hurts more than helps. Even Oracle with decades of engineering behind it doesn't get execution plans correct 100% of the time and provides a way to tune query execution via hints.

However, TIL that https://github.com/ossc-db/pg_hint_plan exists so that will probably become a standard thing I deploy.

With PostgreSQL, the extension to do it, pg_hint_plan is really good, but not widely used because not included in the core, not even in contrib. The consequence is that people install it only when needing it, without the time to learn hot to hint properly, may think that "my hint is not used" and give up.

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

there is a maintained index hint extension: https://github.com/ossc-db/pg_hint_plan - at least as far as 13 (and likely 14).

if we're going to talk about index functionality that would be good and effective for Postgres, an index across all partitioned tables (both normal and unique) would be very much welcomed.

the problem is finding someone to maintain it for life.