5 ms·
There's only one PostgreSQL incident story, retold countless times. There's a bad query plan, we know what the right plan is, the tools to detect the problem ar
by electroly 5y ago
There's only one PostgreSQL incident story, retold countless times. There's a bad query plan, we know what the right plan is, the tools to detect the problem are poor, and PostgreSQL won't let us force a fixed plan, so we have to do some unholy hack to nudge it towards the right plan. They even relied upon another pretty substandard aspect of PostgreSQL: the fact that CTEs are optimization barriers. For god's sake, it's 2022.
On SQL Server, we see it building the bitmap in the graphical query plan basically right away. We can see it in Query Store, and we can see it in sp_whoisactive. The actual query plan being used in production is listed. Then, a couple minutes later, you've deployed a query hint and the bitmap is gone. This is not a hypothetical scenario I am imagining; I have done exactly this, for exactly this problem with the query plan trying to build a bitmap on a huge table. This is a minor blip for SQL Server users and not a disaster that turns into a blog post.
At what point does PostgreSQL change their minds about officially supporting query hints in the base system? This isn't the first time I've read basically this exact blog post and then written this exact comment on HN.
- vore 5y agoActually there's two ;-) The other one of course being the notorious txid wraparound!
- NicoJuicy 5y agoAnekdote: Recently fixed an issue for performance ( MS SQL ) by just using "With INDEX index-name". Very small PR actually. I couldn't write an blog post about it actually.
- magicalhippo 5y agoI mentioned it in an earlier thread, but why can't we just select from indexes directly as if they were tables or views? I mean sure, most of the time the DB is clever. But sometimes the developer knows best, why not provide the tools? select i.OrderNumber, i.OrderDate, o.OrderShipped from ix_Orders_OrderDate i join Orders o on o.OrderNumber = i.OrderNumber where i.OrderDate > '2022-02-01'
- electroly 5y agoIndex names aren't global; they are table-scoped. So you have to specify the table name. If you squint your eyes, there's not much difference between typing "ix_Orders_OrderDate i" and typing "Orders i WITH (INDEX(ix_Orders_OrderDate))". SQL Server lets you do this.
- jeltz 5y agoNo, index named are global in PostgreSQL and the SQL standard.
- electroly 5y agoThe ANSI SQL standard doesn't talk about indexes at all. I'm sure you're right about PostgreSQL; I'm no expert on that implementation. I took his post to be asking about SQL Server since that's what we were talking about in the subthread (hence my SQL Server-specific response). In SQL Server indexes are table-scoped.
- orobinson 5y agoYup I’ve had to debug an issue exactly like this. An IN statement that when populated with <7 parameters would happily use an index but when populated with >=7 parameters would decide to do a full table scan. I was quite dismayed to discover Postgres doesn’t support index hints like MySQL does when I came to fix it.
- deleted 5y ago[deleted]
- tenken 5y agoDoes MySQL run into these types of issues? ... I've yet to run into this, but I believe MySQL supports query hints as well.
- albertopv 5y agoThis. I have years of experience on Sql Server and just recent months on Postgresql, but I found the latter lacks simple and easy troubleshooting tooling. Bad query plan issues in Sql Server are not really an issue, something like the problem of this post would have take minutes to discover and fix using query store, that you can turn on and off when needed (I usually left it on, space is capped). Also if there were multiple plans computed and cached you could have forced sql server to use a specific one until a proper fix was deployed.