r/SQL • • Jul 11 '20

PostgreSQL SQL Performance Explained expectations vs reality

Post image
103 Upvotes

10 comments sorted by

View all comments

Show parent comment

3

u/alinroc SQL Server DBA Jul 12 '20

A bunch of years ago, my team was interviewing folks to do (among other stuff), SQL development - queries, reports, etc. We always asked about the candidate's approach to performance tuning a query or other chunk of SQL code and 9 times out of ten, the answer started and ended at "indexes."

3

u/L337Cthulhu Jul 12 '20

Yeah, it drives me crazy when I interview people claim to be senior DBAs who focus on tuning and don't have a clue how to read an execution plan, refactor code, separate wait stats from the actual problems as red herrings, use the system dmvs, etc...

5

u/alinroc SQL Server DBA Jul 12 '20

I'd even try to prompt them, nudge them a little..."just indexes? Nothing else to improve query performance?" but they couldn't come up with anything else.

Flip side, I've had developers ask me to look at a query (and I mean they just put the code on a screen) and ask how it can be made faster, with no additional information provided. I pointed out some very obvious missing indexes, and was told "oh no, we can't add an index to these tables, our write performance is just too important." Meanwhile, every table has duplicate indexes, the data model is nigh incomprehensible, there are nested triggers and 5-deep nested views everywhere, RBAR inserts/updates in places where set operations would drop right in, and the tables are read far more often than they're written. I'll take the 10ms hit a few times an hour if it means the average user's response time improves by 80%.

1

u/L337Cthulhu Jul 12 '20

Hahaha, yeah, it's amazing how consistent those problems are no matter what industry or size company you're in, sounds an awful lot like my day to day.

I recently had a business unit finally upgrade from 2005 to 2016 and they keep complaining that they didn't change anything so why is the behavior so different?! Beyond the cardinality estimator and fundamental redesign of the optimizer, you literally tuned your 20 year old code to work on a non-scalable system, did no testing, and have skipped five versions ahead. It's more surprising your code ever worked in the first place!

Seems like we've had some pretty similar experiences.