> While I'm sure it's just because of the simplicity of this example, it
> seems that the query could be reorganized to avoid this double query:
> select * from foo F, bar B where B.name = 'bla' and (B.name = F.name or
> B.type = F.type);
That was the original format of the query, and while not obvious, forced a full
table scan on foo and bar. The query presented is the result of many iterations
of "explain" and execution timings. Even with sequential scans disabled,
Postgres still does them.
> (granted that this gives a slightly different results, rows matching
> both conditions don't appear twice, which I would imagine to be an
> unwanted side effect of the original query, anyway).
> I would guess it's easier for the query writer to figure this out than
> the db itself, however, since the two queries look very different (and I
> suppose the database wouldn't come up with this query since the result
> /is/ different). Here's a new question: Would it be useful for the
> database to try and simplify queries before executing them? Or would
> this just take more time than it's worth for most cases?
> According to EXPLAIN, it /plans/ on doing the query twice, but I don't
> know enough about the internal workings to know if it caches results (so
> I can't answer the original question, sorry).
That's the problem I see as well. You would think that if Postgres sees the
same subquery, it should only do it once. Oh well, neither does it seem Oracle.
5-4-3-2-1 Thunderbirds are GO!
In response to
pgsql-hackers by date
|Next:||From: Richard Huxton||Date: 2001-07-24 12:58:29|
|Subject: Re: Bad timestamp external representation|
|Previous:||From: tamsin||Date: 2001-07-24 09:15:42|
|Subject: Bad timestamp external representation|