Thread: [GSoC] Optimization tasks

Hi Camelia!

Thanks for your interest in PostgreSQL!

* Camelia C (camelia13c@gmail.com) wrote:
> My name is Camelia, I carry out Doctoral/PhD research in the field of
> database query processing and I'd like to contribute with PostgreSQL
> optimization tasks during this year's GSoC:
>
>    - Optimization- add to the join removal code the ability to remove joins
>    to tables when the join is on a foreign key column (where all entries must
>    exist in the parent anyway)

There has actually been quite a bit of discussion about exactly that,
along with various patches proposed.  This work has been lead by David
Rowley and the discussion can be found here:


http://www.postgresql.org/message-id/flat/CAApHDvocUEYdt1uT+DLDPs2xEu=v3qJGT6HeXKonQM4rY_OsSA@mail.gmail.com#CAApHDvocUEYdt1uT+DLDPs2xEu=v3qJGT6HeXKonQM4rY_OsSA@mail.gmail.com

>    - Optimization- Check the set of conditionals on a WHERE clause against
>    CHECK constraints on the table being queried and remove any conditionals
>    which *must* be true due to the CHECK constraints

This is an interesting optimization that I don't believe anyone is
currently working on.  We do look at WHERE clauses and compare them
against the CHECK constraints for the purposes of working out if the
WHERE clause could ever possibly be true (and, if it can't, then we omit
that table from consideration for the query), but I don't believe we go
the other direction and remove the conditional check when the CHECK
shows that the conditional must always be *true*.

I'd be very interested in understanding where that particular
optimization is relevant (and that will be an important factor which
will be considered when deciding if this is a worthwhile effort).

> and others that may appear interesting along my review of the source code.

I'd certainly be happy to discuss any other thoughts you have along
these lines.  Note that while we are always interested in improvements
to our optimizer, we are also concerned with the length of time that it
takes for the optimizer to decide upon a plan.  We won't include
optimizations which have a high run-time cost unless there is a high
probability that they result in a query execution run-time which is much
better.

> I'm very excited about this opportunity and I hope that the results will be
> as good!

It's great to see your interest in the optimizer!

> Will the aforementioned subjects be open for work or do other committers
> have them under work already?

Per above, the first is actively being worked on, but not the second.

> Do you have a specific template for the project proposal?

I don't believe we do.  Hopefully Thom or one of the other admins will
correct me if I got that wrong. :)

    Thanks!

        Stephen

Camelia,

While that provides a guideline for what you'll want to include in your
proposal, I believe you have some amount of freedom both in how much you
cover each of those topic areas and the ordering of them.  You are also
welcome to include any other information which is relevant and supports
your proposal.

    Thanks!

        Stephen

* Camelia C (camelia13c@gmail.com) wrote:
> Hello,
>
> For my second question, I guess that [1] provides the information of the
> expected parts of the project proposal.
>
> However, I expect some feedback from your part about the topic chosen
> before I jump into writing the project proposal.
>
> Thanks!
>
> Have a nice week-end,
> Camelia
>
> [1] http://www.postgresql.org/developer/summerofcode/#proposals
>
>
> ---------- Forwarded message ----------
> From: Camelia C <camelia13c@gmail.com>
> Date: Thu, Mar 5, 2015 at 3:54 AM
> Subject: [GSoC] Optimization tasks
> To: pgsql-students@postgresql.org
>
>
> Hello,
>
> My name is Camelia, I carry out Doctoral/PhD research in the field of
> database query processing and I'd like to contribute with PostgreSQL
> optimization tasks during this year's GSoC:
>
>    - Optimization- add to the join removal code the ability to remove joins
>    to tables when the join is on a foreign key column (where all entries must
>    exist in the parent anyway)
>    - Optimization- Check the set of conditionals on a WHERE clause against
>    CHECK constraints on the table being queried and remove any conditionals
>    which *must* be true due to the CHECK constraints
>
> and others that may appear interesting along my review of the source code.
>
> I'm very excited about this opportunity and I hope that the results will be
> as good!
>
> Will the aforementioned subjects be open for work or do other committers
> have them under work already?
> Do you have a specific template for the project proposal?
>
> Thank you very much!
>
> Best regards,
> Camelia