Database performance recommendations (by Azure PSQL)

Dev
1

Hi,

we’re hosting our discourse-based forum on azure with a “Azure Database for PostgreSQL” instance. One of the advantages of this service are the performance recommendations based on “database characteristics” and usage.
Since migrating to azure we’ve received some hints about missing and presumably redundant indexes I like to share with you.
We’ve seen a slight (not much) overall performance increment after creating these missing indexes but couldn’t apply the DROP INDEX - recommendations as it would/will potentially break upcoming updates/db-migrations. Anyway, we can’t tell if these indexes are save to be deleted or if it will have a negative effect on performance.

Here are the indexes azure suggested us to create (impact “low” and “moderate”):
create-indexes.csv (1.5 KB)

These are the recommendations for “already covered” indexes:

Summary

Drop index index_optimized_images_on_upload_id on table public.optimized_images.
Reason: Index is covered by index_optimized_images_on_upload_id_and_width_and_height

Drop index index_post_revisions_on_post_id on table public.post_revisions.
Reason: Index is covered by index_post_revisions_on_post_id_and_number.

Drop index post_timings_summary on table public.post_timings.
Reason: Index is covered by post_timings_unique.

Drop index index_topic_links_on_topic_id on table public.topic_links.
Reason: Index is covered by unique_post_links.

Drop index index_post_uploads_on_post_id on table public.post_uploads.
Reason: Index is covered by idx_unique_post_uploads.

You should take these “DROP” recommendations with a grain of salt as I think it will only affect the overall DB size instead of increasing performance. For example: Our whole DB uses ~120GB and all of these recommendations will free 422MB. Azure itself defines the impact as “low”. So it seems it’s nothing more than a cleanup.

As said above I just wanted to share this with you as it may help to tune the performance a little bit. Besides these indexes there where no other recommendations azure provided for the time we’re using their services.

Kind Regards
Sascha

8 Likes
2

Thanks for sharing, some of the recommendations are a bit odd…

CREATE INDEX index_topics_on_category_id ON public.topics USING btree (category_id)
CREATE INDEX index_topics_on_category_id_and_id ON public.topics USING btree (category_id, id)
CREATE INDEX index_topics_on_id_and_category_id ON public.topics USING btree (category_id, id)

This triplicate dupe index it is suggesting has extremely low selectivity, the trouble with indexes like that is that the planner correctly tends to ignore them. If there are only 10 different values you need to mix it up with something else like say topic_id

As to underused indexes, about 6 months ago we ran some stats on the database looking for indexes we did not touch in our infrastructure, we did prune a few but this topic is a good reminder for us to run through this process again.

Thanks for posting this.

4 Likes
3

Thanks for your reply and pointing me to these 3 indexes. I haven’t noticed this triplicate as the recommendations didn’t came in at once and I’ve applied them piece by piece over weeks.
Even it’s quite strange that they don’t occur within the DROP recommendations. Seems Azure still has some work to do :smiley: (and I have to check those recommends twice).

Kind Regards

1 Like