Skip site navigation (1) Skip section navigation (2)

Re: sub queries and caching.

From: mlw <markw(at)mohawksoft(dot)com>
To: TenToThe8th(at)yahoo(dot)com, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: sub queries and caching.
Date: 2001-07-24 09:24:10
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
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 = 'bla' and ( = 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 HuxtonDate: 2001-07-24 12:58:29
Subject: Re: Bad timestamp external representation
Previous:From: tamsinDate: 2001-07-24 09:15:42
Subject: Bad timestamp external representation

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group