Re: WIP: cross column correlation ...

On Tue, Feb 22, 2011 at 9:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)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