Re: Fwd: Query Optimizer Postgresql

Hi Sumit,

Unfortunately, those questions seem rather vague and generic, to the
extent that it's virtually impossible to answer them without speculating
what your "join order optimization" might do ...

Generally speaking, paths are the primary output of a planner, so if all
you do is constructing alternative paths, you should be OK modifying the
places that you mentioned. Or maybe you'll need to tweak the cardinality
estimation / costing places, it's hard to say.

I suggest you try writing some code for the join optimization, and then
ask about issues you run into - with code examples etc.

regards

On 10/17/2018 06:42 AM, Sumit Chaturvedi wrote:
> Hello Everyone,
> I had some questions about the query optimization engine and will be
> grateful if someone can answer them
>
> ---------- Forwarded message ---------
> From: *Sumit Chaturvedi* <sumit(dot)chaturvedi(at)gmail(dot)com
> <mailto:sumit(dot)chaturvedi(at)gmail(dot)com>>
> Date: Sun, Oct 14, 2018 at 8:50 PM
> Subject: Query Optimizer Postgresql
> To: <bruce(at)momjian(dot)us <mailto:bruce(at)momjian(dot)us>>
> Cc: Adwait Godbole <godbole15(at)gmail(dot)com <mailto:godbole15(at)gmail(dot)com>>,
> Nilay Pande <nilay017(at)gmail(dot)com <mailto:nilay017(at)gmail(dot)com>>,
> <nitishj(at)cse(dot)iitb(dot)ac(dot)in <mailto:nitishj(at)cse(dot)iitb(dot)ac(dot)in>>
>
>
> Hello Sir
>
> My friends and I are trying to come up with an experimental
> implementation of Join Optimization within PostgreSQL. We have a few
> questions about the code and will be grateful if you could address them.
>
> We found that the dynamic programming algorithm is implemented in
> standard_join_search(). We realized that the optimal path is stored in
> the cheapest_path field in the RelOptInfo struct that is returned.
>
> Since in our implementation, we are only trying to optimize on the join
> order, we were wondering what all changes would we need to make without
> breaking the code? In other words, what additional state would we need
> to modify if we were to rewrite the standard_join_search() method such
> that everything works out well.
>
> Also, since our implementation would need the stats generated by ANALYZE
> command, what interface is available for that. For example I noticed the
> function call set_cheapest(rel). Can I simply use this call once I have
> populated my RelOptInfo->pathlist and accept it to consult the
> statistics and appropriately populate RelOptInfo->cheapest_path.
>
> Finally, what additional care should we take to handle queries
> containing asymmetric joins or joins in subqueries?
>
> By the way, I have watched your talks on Postgres Internals and the
> Query Optimization Engine and as a student of databases, I find them
> enlightening.
>
> Thank You
>
> --
> Sumit Chaturvedi
>
>
> --
> Sumit Chaturvedi

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services