Thread: Does PostgreSQL support EXISTS?
The Subject says its all. --Raymond
Attachment
On Wed, Jun 13, 2001 at 10:03:24AM -0400, Bruce Momjian wrote: > > > select x from a where v in (select v from b) > > > select x from a where exists (select 1 from b where a.v = b.v) > > > > The latter should be faster than the former on every relational database > > system. > > That surprises me because the subquery is a correlated subquery which > are usually slower on other databases that normal subqueries. To be honest I didn't notice that. :-) I was just talking about the difference with IN (where you have to compute the complete result set) and EXISTS where you just look for one match. Michael -- Michael Meskes Michael@Fam-Meskes.De Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
> On Wed, Jun 13, 2001 at 10:03:24AM -0400, Bruce Momjian wrote: > > > > select x from a where v in (select v from b) > > > > select x from a where exists (select 1 from b where a.v = b.v) > > > > > > The latter should be faster than the former on every relational database > > > system. > > > > That surprises me because the subquery is a correlated subquery which > > are usually slower on other databases that normal subqueries. > > To be honest I didn't notice that. :-) > > I was just talking about the difference with IN (where you have to compute > the complete result set) and EXISTS where you just look for one match. When you use IN and a subquery you _sometimes_ have to execute the subquery for every row of the outer query. Ouch! It can be optimized to run the subquery non-correlated and join the correlated values to the outer query for specific rows. The trick is knowing when the subquery is going to be run many times and when the subquery is going to be run for only a few rows so the optimization is not used. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I thought NOT IN was the only one that was concerned about any NULL?
No, they both are: in the presence of NULLs, IN can return TRUE or NULL,
NOT IN can return FALSE or NULL.
The reason the FAQ is always about NOT NULL is that WHERE treats NULL as
FALSE, so the average newbie writing an IN doesn't even realize he's
getting a NULL rather than a FALSE. With NOT NULL, he can't ignore it.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > I thought NOT IN was the only one that was concerned about any NULL? > > No, they both are: in the presence of NULLs, IN can return TRUE or NULL, > NOT IN can return FALSE or NULL. > > The reason the FAQ is always about NOT NULL is that WHERE treats NULL as > FALSE, so the average newbie writing an IN doesn't even realize he's > getting a NULL rather than a FALSE. With NOT NULL, he can't ignore it. Got it. How does an IN subquery returning NULL behave differently from one returning FALSE? I can't think of a test that would be affected. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Got it. How does an IN subquery returning NULL behave differently from
> one returning FALSE? I can't think of a test that would be affected.
After we fix IS TRUE and friends to respond to nulls correctly (Conway's
promised to do that, IIRC) it'll be possible to write
(foo IN (SELECT ...)) IS NOT FALSE
and get the "intuitive" behavior. But right now that doesn't work.
Hm. Maybe we could recognize that construct as a whole, and translate
it to an optimizable join? It'd become the usual locution, I imagine.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Got it. How does an IN subquery returning NULL behave differently from > > one returning FALSE? I can't think of a test that would be affected. > > After we fix IS TRUE and friends to respond to nulls correctly (Conway's > promised to do that, IIRC) it'll be possible to write > > (foo IN (SELECT ...)) IS NOT FALSE > > and get the "intuitive" behavior. But right now that doesn't work. OK, so I wasn't missing anything in our current code. I can see how this capability would change things. > Hm. Maybe we could recognize that construct as a whole, and translate > it to an optimizable join? It'd become the usual locution, I imagine. Are we anywhere with optimizing IN to EXISTS? I didn't think there was any work being done in that area. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Are we anywhere with optimizing IN to EXISTS? I didn't think there was
> any work being done in that area.
I don't think it's that hard to do within the outer-join mechanism,
except for this little issue about NULLs. The planner already makes
querytree alterations of comparable complexity, to deal with views and
subselects...
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Are we anywhere with optimizing IN to EXISTS? I didn't think there was > > any work being done in that area. > > I don't think it's that hard to do within the outer-join mechanism, > except for this little issue about NULLs. The planner already makes > querytree alterations of comparable complexity, to deal with views and > subselects... Doing that would elimate the need for query sources, at least for a while. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Are we anywhere with optimizing IN to EXISTS? I didn't think there was
> any work being done in that area.
> Doing that would elimate the need for query sources, at least for a
> while.
Uh ... what? I don't see a connection.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Are we anywhere with optimizing IN to EXISTS? I didn't think there was > > any work being done in that area. > > > Doing that would elimate the need for query sources, at least for a > > while. > > Uh ... what? I don't see a connection. Are we talking about converting IN to EXISTS for performance reasons? I though having query tree sources was required for this? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Are we talking about converting IN to EXISTS for performance reasons?
No, we're talking about implementing both IN and EXISTS as special forms
of join.
regards, tom lane
> Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Got it. How does an IN subquery returning NULL behave differently from > > one returning FALSE? I can't think of a test that would be affected. > > After we fix IS TRUE and friends to respond to nulls correctly (Conway's > promised to do that, IIRC) it'll be possible to write > > (foo IN (SELECT ...)) IS NOT FALSE > > and get the "intuitive" behavior. But right now that doesn't work. > I'm still committed to it -- just want to finish up has_table_privilege first -- shouldn't be too long now. -- Joe
I just ran into an interesting problem. My /var partition was full, and the culprit turned out to be the postgres log file, which was only evident through lsof. It was actually a deleted file which was being kept open by the running process. The interesting thing is that if I redirect the output of postmaster, then every connection has this file open. My environment is Apache/jserv/postgres. So in order to rotate the postgres log, I would have to 1) Shut apache down, 2) Shut jserv down 3) Shut postgres down rotate the logs, then bring them all back up again Is there a way to tell postgres to release the log file, or am I completely off base here? Dave
Answer: Use syslog. -alex On Wed, 13 Jun 2001, Dave Cramer wrote: > I just ran into an interesting problem. My /var partition was full, and the > culprit turned out to be the postgres log file, which was only evident > through lsof. It was actually a deleted file which was being kept open by > the running process. > > The interesting thing is that if I redirect the output of postmaster, then > every connection has this file open. > > My environment is Apache/jserv/postgres. So in order to rotate the postgres > log, I would have to > > 1) Shut apache down, > 2) Shut jserv down > 3) Shut postgres down > rotate the logs, then bring them all back up again > > Is there a way to tell postgres to release the log file, or am I completely > off base here? > > Dave > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > >