Re: Optimizer improvements: to do or not to do?

Joshua Reich <josh(at)root(dot)net> writes:
> I lurk... I don't know if I'm a 'statistics jock', but I may be
> valuable if only I had a better understanding of how the optimizer
> works. I have been following this thread with interest, but could really
> do with a good pointer to background information beyond what I have read
> in the main postgres manual. Does such information exist, and if so,
> where ?

Well, there's the 20000-foot view here:
http://developer.postgresql.org/pgdocs/postgres/planner-optimizer.html
but after that you have to start reading code.

The optimizer README file may be useful:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/README
but it goes into a lot of details that probably aren't interesting for
your purposes. Most of the planner is just mechanism associated with
generating different possible plans. The policy that determines which
plan is chosen is the cost-estimation equations, and those are all in
costsize.c and selfuncs.c:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/path/costsize.c
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/selfuncs.c
The division between these two files is a bit historical, but roughly
speaking selfuncs.c knows about the behavior of specific WHERE-clause
operators and index access methods, while costsize.c knows about the
behavior of particular plan types.

I'd like to think that costsize.c is well enough commented that you can
follow it even without any C knowledge, but selfuncs.c may be a bit more
daunting. Still, the comments are pretty extensive, and feel free to
ask questions on pg-hackers.

regards, tom lane