Re: Design: Escort info from WHERE clause to executor?

From: peter(dot)trautmeier(at)gmx(dot)de
To: pgsql-hackers(at)postgresql(dot)org
Subject: Re: Design: Escort info from WHERE clause to executor?
Date: 2007-07-25 13:14:12
Message-ID: 20070725131412.125820@gmx.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers


Von: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
> peter(dot)trautmeier(at)gmx(dot)de wrote:
> > To sum up, I am looking for a (decently efficient) scheme that is able
> to
> >
> > (1) pass arbitrary conditional expressions from WHERE to the executor in
> a structure preserving way.
> > (2) annotate arbitrary expressions with weights that survive on its way
> from the parser to the executor.
> > (3) access the logical value of particular subexpressions.
> >
> > I have some basic ideas how at least some of the requirements might be
> achieved. But as I am not totally satisfied with my ideas I hope you can
> provide me with some fresh input.
>
> Why? What are you trying to achieve?

I am implementing a technique that sorts a result set according to weight annotations in the WHERE.

The query

SELECT * FROM cars
WHERE (cdChanger=1){2}
OR (mp3player=1){1}

would be sorted according to partial conditions that hold.

Cars that have both a CD changer AND a MP3 player get a weight of 3, i.e. (2+1).
Cars that only have a CD changer get a weight of 2.
Cars that only have a MP3 player get a weight of 1.
Cars that have neither a CD changer nor a MP3 player do not belong to the result set anyway.

I have to sort the tuples according to their individual weight - that is why I need to annotate arbitrary expressions with weights.

This is a simple example, but in case of cascaded ORs and ANDs the semantics gets slightly trickier - that is why I need the structure of the original WHERE clause preserved.

The executor as it stands now is evaluating quals in a short circuit manner, and that is totally feasible: As soon as a single subexpression of and ANDed qual is false, it stops.

However, in order to sort the tuples in the result set I generally need to know the logical values of all subexpressions, or at least more of them as the executor needs for its rather coarsely grained decision 'belongs-to-result-set' or 'does-not-belong'.

In general, I have to evaluate the original WHERE expr tree level by level, starting at the top(root) that represents the whole condition and possibly visiting and evaluating every single subexpression in the tree to every leaf, until I have enough information to compare two tuples with each other.

I hope I got the basic idea across, but please don't hesitate to ask for more details if I failed to paint the picture clearly enough. I appreciate your interest.

(I know that seems complicated, and indeed I am getting the feeling that this _is_ complicated. But hey, then again it's my duty ;) )

Regards,
Peter

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2007-07-25 13:51:38 Re: Design: Escort info from WHERE clause to executor?
Previous Message peter.trautmeier 2007-07-25 12:34:23 Re: Design: Escort info from WHERE clause to executor?