Tatiyants

This is the best tool I know, works pasting only the JSON output: http://tatiyants.com/postgres-query-plan-visualization/ The information density the rendering gives me is way better than the flamegraph.

Cool project. Not a DBA but was interested in playing around with this. Be great to maybe add some example plans here: http://tatiyants.com/pev/#/plans So if you just want to checkout the interface you can click to load up an example or two.

Other tips: - Change the field type from JSON to JSONB (better storage and the rest) https://www.postgresql.org/docs/13/datatype-json.html -Learn the json in-build functions and see if one of them can replace one you made ad-hoc - Seriously, replace json with normal tables for the most common stuff. That alone will speed up things massively. Maybe keep the old json around in case, but remove when it become old(?) - Use views. Views allow to abstract over your databaee and allow to change internals - If a big things is searching and that searching is nkind of complex/flexible, add FTS with proper indexing to your json then use it as first filter layer: SELECT .. FROM table WHERE id IN (SELECT .. FROM search_table WHERE FTS_query) AND ...other filters This speeeeeedupppp beautifully! (I get sub-second queries!) - If your query do heavy calculations and your query planner show it, consider move them into a trigger and write the solved result into a table, and query that table instead. I need to loans calculations that requiere sub-second answers and this is how I solve it. And for your query planner investigation, this handy tool is great: https://tatiyants.com/pev/#/plans

What helped me a lot getting away from being limited by ORM capabilites while on the job: - Get the raw SQL of some slow or memory intensive queries the ORM produces and try to optimize them by hand. Try different approaches to get the same result, measure and understand them by using 'explain' and visualizers like http://tatiyants.com/pev/ This works great together with libraries like sqlalchemy where the ORM is optional and build upon a abstraction of SQL that you can use directly. This way you can use the ORM for the 80% where it works just fine and hand write the rest in Python without having to deal with raw, fairly inflexible SQL in application code. - Try moving workload from the application to the database. In the past I often ended up doing ORM queries to get a large numbers of objects and then further process and even join them in Python. In Most of these cases doing it in the database is way more efficient and lets you get away with a slow language, synchronous requests running on small servers for a surprisingly long time. - Do business intelligence type queries for reports and monitoring. Through doing this I discovered a lot of database features that I didn't encountered commonly in web application development but that nonetheless came in handy several times for it. Also since you often need to combine data in ways that it wasn't necessarily originally designed for, you really need to start thinking about how your data is structured and how to get it in and…

My previous job(s) were primarily MSSQL and I was excited to get to use PG in my current position. In general I agree with the premise of the article - for just about everything PG is better. For me, the only places where MSSQL has an edge is: - Management Studio is superior to any standard query manager ive found for PG. I currently use what is in intellij (which is similar to their datagrip product) which I find perfectly acceptable and in some ways superior to Management Studio, but PGAdmin isn't in the same ballpark. - MSSQL visual query explainer is great - ive found some alternatives (http://tatiyants.com/pev/#/plans) and in general ive gotten much better at reading and understanding the text output of PG's explain, but I do miss MSSQL in this regard. - MSSQL CTE's not being optimization fences meant that I got to use them in more places. But I can do more things with CTE's in PG (such as using an ORDER BY) - this is relatively minor but annoying - readability with CTE's vs subqueries is much improved. - Snapshot and transaction logs with MSSQL is much better than any alternative ive found with PG, but tbf I haven't looked into it very far. When it comes up though it would be really nice to be able to restore to point in time easily with PG. - I do occassionally come across missing functions in PG, but thats getting more and more rare. - Cloud offerings - they exist for PG and are great for what they offer, but many extensions are not available or not fully…

> what is the distinction between "system-level" and "application-level"? I think it mostly comes back to scope or encapsulation. I expect my host to provide facilities external to my program (databases, sendmail, a webserver), and I expect to have control over the software libraries internal to my program. Perhaps you're also asking why APT or Yum, which are great at managing system-level package availability and versioning, couldn't be adapted for local use by applications. I'm not sure there's a good answer for that. Maybe it's just portability? If BlubLang runs on 9 platforms, then BlubPM needs to run on those same 9 platforms. It's probably easier to get there if BlubPM is written in BlubLang. > What happened in the past 10-15 years that changed the world so much that whereas before we ran our applications on top of the system, now we seem to want to run them in individual sandboxes, often inside of other sandboxes inside of other sandboxes? VMs? I once heard a terrible analogy to the effect that we used to treat servers like pets: we gave them cute names and we nursed them back to health when they got sick. Now we treat them like livestock: if one gets sick, you shoot it and get a new one. There's a hilarious send-up of hand-maintained systems in the "DevOps is Ruining My Craft" article at http://tatiyants.com/devops-is-ruining-my-craft/ The more you isolate the applications from the host, the easier it is to redeploy them.

> Hence, no tree-of-objects database engine provides the level of detail in their "EXPLAIN" output that SQLite provides. I've seen some cool graphical visualisation tools for postgres http://tatiyants.com/postgres-query-plan-visualization/

An alternative to https://explain.dalibo.com/ is https://tatiyants.com/pev Both have pros and cons about how they visualize things.

I’d start w ‘EXPLAIN query’, if you arent familiar with the output there, you can put it on PEV and get a visualization. https://tatiyants.com/pev/#/plans

I don't think this is exactly what you described, but you may find it valuable: it's a tool that produces a visualization of a postgres query plan https://tatiyants.com/pev/#/plans/new (click "create sample plan" in top right and then "submit" to see an example)

I've had many similar experiences with Postgres. I always seem to be the one adding the query logging to the apps. Then I feed the prod queries into the query planner. Then run the results through this tool: http://tatiyants.com/pev/#/plans/new Tweak the queries, add/remove indexes. Voilà.

Alternative that also works well: https://tatiyants.com/pev/#/plans

We are inspired by https://explain.depesz.com/ and https://tatiyants.com/pev/ and would be very interested in how people think pgMustard compares. Thanks!

Heard of depesz; haven't used it. I rather like the PostgreSQL Explain Vizualizer. [1] I can keep multiple plans around, and it has a lot of detail. [1] http://tatiyants.com/pev [2] https://github.com/AlexTatiyants/pev

Inspired from it you also have pev if you prefer something more visual : http://tatiyants.com/postgres-query-plan-visualization/

I've enjoyed using pev[1]. It's based on this tool but lays out the plan graphically. [1] http://tatiyants.com/pev/#/plans/plan_1550754076978

It's a bit off topic since it's not a resource for learning, but: http://tatiyants.com/pev/#/plans (Postgres Explain Visualizer) When dealing with query optimization in practice, it helps a lot since it enables a clear, convenient and synthetic view of an EXPLAIN (ANALYZE). Just yesterday, it helped me improve a big analytic query from executing in 10 minutes to 5 seconds by just adding a small index.

Good addition! Here are some more: https://ferd.ca https://blog.filippo.io https://lethain.com http://movingpackets.net https://www.integralist.co.uk https://peter.bourgon.org/blog https://engineering.linkedin.com/blog https://begriffs.com https://mijailovic.net http://tatiyants.com http://blog.pentestbegins.com (seems down right now?) https://kalzumeus.com/archive http://blog.stephenwolfram.com https://paragonie.com/blog https://www.filfre.net https://brandur.org/articles http://archagon.net https://eklitzke.org I guess I could go on forever…

For visualizing EXPLAIN there is a cool tool: http://tatiyants.com/pev/

That's a perfect foundation for Guilt-Driven Development. http://tatiyants.com/guilt-driven-development

I am for one excited it is available as a "web" app and are going in a new direction. PGadmin was functional, I'll give it that, but it paled in comparison to MySQL's tools. If this can speed up development and make it better then I'm all for it. Maybe they could integrate this[1] into it? 1. http://tatiyants.com/postgres-query-plan-visualization/

Maybe this one http://tatiyants.com/postgres-query-plan-visualization Original post at https://news.ycombinator.com/item?id=10960344

That page yields a 504 Gateway Time-out right now. Cached at http://webcache.googleusercontent.com/search?q=cache:jH9Wl8R0E1cJ:tati… The demo at http://tatiyants.com/pev/#/plans works. Unfortunately it has some glitches. Hints for using it: * In psql use \o plan.txt to redirect the output of explain to a file. It will be a long output because you must use EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) and copying it from the terminal won't be fun. * Remove the first two lines (header) and the last two (footer) leaving only the json data. Remove all the + characters at the end of every line. Suggestion to the author: the tool should handle that. That said, it works. It really is much clearer than the output of explain in the terminal and as a result I'm googling how to speed up sort now. Thanks.

I experimented with making a live updating HTML editor before, and one of the biggest problems with it is that sometimes your code will go through an intermediate stage, while you're typing it, where it contains infinite loops. Now, if you could use JS.js¹ to sandbox the javascript in a webworker so that it never blocks the editor, and kill+reload every time you enter a key, well that would be cool. 1: http://tatiyants.com/introducing-js-js/

I'm a big fan of this one: http://tatiyants.com/pev/#/about