Re: sub queries and caching.

Kevin wrote:
>
> 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!
------------------------
http://www.mohawksoft.com