Hi,

I was just approached with an optimizer question I cannot answer. Does
our optimizer know how expensive different comparisons are? That is can
it do something like:

If there are different comparisons in a where clause check the ints
first, then the strings, then everything with regexp, or like clauses
etc. and finally function calls at last, because in most cases a
function call is the most expensive one. 

Okay, we may argue whether a regexp is more expensive than a function,
but you get the idea. Are we able to get that logic in where clauses
where no index is defined? I just tried a query that given the order in
the where clause either takes a few seconds or forever. Well, I killed
the query after quite some time.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Fri, 18 Oct 2002 19:02:31 +0200
Michael Meskes <meskes@postgresql.org>
optimizer question
Michael Meskes <meskes@postgresql.org> writes:
> I was just approached with an optimizer question I cannot answer. Does
> our optimizer know how expensive different comparisons are?

It does not, because there's noplace it could look to find the info.

> If there are different comparisons in a where clause check the ints
> first, then the strings, then everything with regexp, or like clauses
> etc. and finally function calls at last, because in most cases a
> function call is the most expensive one. 

Unfortunately, these all look like function calls to the optimizer.

I have played with the notion of forcing subquery-based WHERE clauses
to the end of the list, but that's about all that we could easily manage
without great leaps of assumptions...

			regards, tom lane

Fri, 18 Oct 2002 14:48:58 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: optimizer question 