On Tue, Feb 22, 2011 at 9:43 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Robert Haas <robertmh...@gmail.com> writes: >> /me prepares to go down in flames. > >> Personally, I think the first thing we ought to do is add a real, bona >> fide planner hint to override the selectivity calculation manually, >> maybe something like this: > >> WHERE (x < 5 AND y = 1) SELECTIVITY (0.1); > > One of the criteria we've always had for a suitable hint-or-whatever- > you-call-it design is that it *not* involve decorating the queries. > There are a number of reasons for that, some of the killer ones being > > (1) People frequently *can't* adjust their queries that way, because > they're coming out of some broken query generator or other. (Crappy > query generators are of course one of the prime reasons for > poor-performing queries in the first place, so you can't write this off > as not being a key use case.) > > (2) Anything we do like that, we'd be locked into supporting forever, > even after we think of better solutions. > > (3) People don't like decorating their queries with nonstandard stuff; > it smells of vendor lock-in. Especially if it's actually SQL syntax > and not comments. Once you put something into the DML it's just too > hard to fix applications to get rid of it (the inverse case of point > #1).
Those are real problems, but I still want it. The last time I hit this problem I spent two days redesigning my schema and adding triggers all over the place to make things work. If I had been dealing with a 30TB database instead of a 300MB database I would have been royally up a creek. To put that another way, it's true that some people can't adjust their queries, but also some people can. It's true that nonstandard stuff sucks, but queries that don't work suck, too. And as for better solutions, how many major release cycles do we expect people to wait for them? Even one major release cycle is an eternity when you're trying to get the application working before your company runs out of money, and this particular problem has had a lot of cycles expended on it without producing anything very tangible (proposed patch, which like you I can't spare a lot of cycles to look at just now, possibly excepted). I agree that if we can get something that actually works that doesn't involve decorating the queries, that is better. But I would surely rather decorate the queries than rewrite the entire application around the problem. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers