When you have a slow query, and you want to see which parts of the plan took the longest or had the least accurate estimates, try pasting the plan into PlanSplainer. It uses a completely different visualization style than explain.depesz.com or explain.dalibo.com. I’m not quite sold on any of them just yet, and it’s nice to have options.
When you have a slow server, and you want to see what queries are running, pg_show_plans is an extension that adds a new system table.
Database administration skills are still needed, writes Adrien Nayrat, who’s concerned about the proliferation of cloud database platforms that purport to be self-managing. I don’t think this is a new problem – there’s always been a dearth of people who know how to make a database reliable and fast. The cloud services reduce the need for reliability tuners, but performance issues are only bigger in the cloud, where bills get big, fast.
Postgres 16 is better at query planning, says David Rowley, who recaps 10 of his favorite optimizer improvements. This is a great rundown, and the only things that would have made this post better are examples of where the changes make things worse. Ah, well, left as an exercise for the reader!
Technique of the Week:
Creating Temporary Functions
If you want to test a new function, you can use the pg_temp schema as explained in these Stack Overflow answers. For example:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE FUNCTION pg_temp.get_users_by_location(parm_location varchar) RETURNS setof users AS $$ select u.* from users u where u.location = parm_location order by u.reputation desc limit 200 $$ LANGUAGE sql IMMUTABLE; |
The function is exclusive to the session that created it, and no other session can query it, even sessions sharing the same login. Also useful to know: two sessions can create two functions with the same name, but different contents. The pg_temp contents are exclusive to each session, so there’s no conflict, and each just only sees their own.