pg_qualstats_example_query truncated return

[Wrytha]

We have some large queries and are trying to develop a solution that will do automatic index recommendations based on the Percona implementation (with some variance for the 2.0.X release of pg_qualstats).

However, when we use the pg_qualstats_example_query it truncates the return query to 2048 characters. This makes the query invalid and so it cannot be used to do an explain to compare with/without the recommended query performance.

I have been unable to locate the reason the return would be truncated. Is there something I am missing that controls the max length of the returned query?

[rjuju]

Hi,

The example queries are currently stored in regular shared memory, so it has to be a fixed size. You can control the maximum size using the regular postgres configuration option `track_activity_query_size'. But keep in mind that it will greatly increase memory consumption.

That being said, I don't know what implementation you're using but I would recommend to use the global index adviser implemented in pg_qualstats itself. See for instance https://rjuju.github.io/postgresql/2020/01/06/pg_qualstats-2-global-index-advisor.html for more details about its approach and how to use it.

[Wrytha]

Thank you for the guidance. I am using the index advisor but need some custom code around it as it suggests (or seems to) indexes that would cause duplication of primary/foreign key/ and other indexes already in place.

pg_qualstats is a great help in the quest to get indexes right 👍

[rjuju]

Thanks.

Yes, it doesn't seem surprising that (partially) redundant indexes can be suggested, at least for the adviser in pg_qualstats.

I will try to implement such a check as soon as I have time for that, but note that even if (partially) redundant indexes were not suggested, you would still need to validate the suggestion and decide if the related queries are important enough to justify adding a new index, or if an index is the best solution to the query problem.

The tool is only designed to help users concentrate on that part, and avoid wasting time on coming up with possible index.