Re:Why PostgreSQL doesn't have query hints
by Ingaz on Saturday February 05, 2011 @03:14AM
(#2182724)
WITH RECOMPILE does opposite effect. It's kind of anti-hint.
You force SQLServer to never use cached plans and regenerate query plan every time you run query. So you have always "plan generation overhead".
Also, such SP can have only 1 running example in server because of SP:Recomile Lock. Kind of singleton in SQLServer.
Re:Why PostgreSQL doesn't have query hints
by chubs on Saturday February 05, 2011 @02:21AM
(#2182673)
You can take NOLOCK out of my cold, dead, hands! That one is a lifesaver for reading from terabyte-sized OLTP tables with 100/sec inserts...
I really like what the guy is saying in the article. However, it's the perennial idealism vs pragmatism argument. And, i'll put my hand up for pragmatism: I've needed hints in the past, because like everything else in this world, query planners simply aren't perfect.
Re:Why PostgreSQL doesn't have query hints
by Ingaz on Saturday February 05, 2011 @03:19AM
(#2182732)
In SQLServer in general it's better to write one time SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED, than write hint NOLOCK agains each table.
Result is the same.
This is not a hint for planner. You can write it for PostgreSQL also (I mean SET TRANSACTION).
In MySQL I've had to use FORCE INDEX usually when sorting on a large set where the primary key was used on the table or was available for sorting but when that wasn't what I needed it for. I used force index on the column I was sorting by and that improved performance by around 50% maybe but then I ended up caching the results because the worst case was still too slow for my taste and they didn't need to change as often as I was executing this query (it was a random sample of the latest actions on a site).
These are edge cases I suppose but I'm glad they're there for me when I need them. I respect their reasons for not including them though as I normally see them used incorrectly or just strangely. (e.g. insert into table (nolock) (id, blah)...)