Piotr,

I've want more details about the query plan being employed. In many
cases the whole select would be turned into an outer join, in which case
I would expect that very little time would be spent running the query if
no rows matched.

If you include the data types of the fields involved, the full query,
and the query plan being used (from doing "set qep" in sql) we might be
able to give you more help.

Robert

-----Original Message-----
From: info-ingres-bounces (AT) kettleriver...ting (DOT) com
[mailto:info-ingres-bounces (AT) kettleriverconsulting (DOT) com] On Behalf Of
Wisniewski, Piotr, (ProService AT)
Sent: 23 April 2009 13:27
To: Ingres and related product discussion forum
Subject: [Info-Ingres] Queries with NOT EXISTS

Consider a query of the type

SELECT ..
FROM ...
WHERE <A>
AND NOT EXISTS (<B>)
;
A is some search condition
B is a subselect

Is there some setting which could make the ingres optimizer
skip evaluating the B part if no rows satisfy condition A ?


Piotr Wisniewski

_______________________________________________
Info-Ingres mailing list
Info-Ingres (AT) kettleriverconsulting (DOT) com
http://www.kettleriverconsulting.com...fo/info-ingres

Wi¶niewski wrote:

SET NOFLATTEN will have that effect. I seriously doubt that it would
always be a net benefit though. There are cases where it makes a big
beneficial difference but they're not too common.

This is not an optimization problem. The optimizer chooses a
plan before either condition can be evaluated.

--
Roy

UK Ingres User Association Conference 2009 will be on Tuesday June 9, 2009
Go to http://www.iua.org.uk/join to get on the mailing list.

On Apr 23, 2009, at 8:53 AM, Wiśniewski, Piotr, (ProService AT) wrote:

[snip]

Interesting, you're getting the old-style flattening where it
materializes
the not-exists condition and matches that up with the rest of the query.
I wonder why it didn't transform that particular not-exists to an
outer join? none of the usual suspects seem to apply (e.g. a
nullable correlation column can suppress the outer join variant,
but it's non-nullable in this case.)

I may have to try your query against a more up-to-date Ingres to
see what happens...

Karl