PostgreSQL Optimizer Methodology

[zhjwpku]

Workshop Title

Robert Haas, CMU Database Group, PostgreSQL Optimizer Methodology

Date: August 2024

Resources

• Video: https://www.youtube.com/watch?v=XA3SBgcZwtE

[zhjwpku]

Some src files you might want to check:

Postgres statistics generator: https://github.com/postgres/postgres/blob/master/src/backend/commands/analyze.c
Selectivity functions and index cost estimation functions: https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/selfuncs.c
Functions for selectivity estimation of array operators: https://github.com/postgres/postgres/blob/master/src/backend/utils/adt/array_selfuncs.c
Routines to compute (and set) relation sizes and path costs: https://github.com/postgres/postgres/blob/master/src/backend/optimizer/path/costsize.c

[zhjwpku]

In Unconference - Stream 2 - 13:15-14:15 - PGCon 2022

There is a discussion on adding schetches for MCV inter-column estimation.

[zhjwpku]

Some advice from Richard Guo on hacking optimizer:

1. read the README thoroughly
2. use git blame to see the commit message about a specific code block
3. the bottom line for adding some optimizer rules is not introduce performance regression

[zhjwpku]

Question:

Why PostgreSQL does not have a vector execution engine[1], there seems to be some efforts happened on hacker list, but goes no where.

Answer:

Supporting vector execution engine will require significant work for both the optimizer and executor.

[1] https://www.postgresql.org/message-id/flat/CAB0yrem3PYu2qQD4%3DJOg8y_5QAK%2BQ%2BK5yEptxs0t71j5cRyoOQ%40mail.gmail.com

[zhjwpku]

Here are some questions asked in the session, but I could not summarize the answers:

1. Is there any infrasture that can help detecting performance regression from optimizer feature?
2. Is there any extra considerations when it comes to Partitioned tables?
3. Somebody mentioned GP orca in the message chat, no specific question.

[zhjwpku]

38.11. Function Optimization Information

[zhjwpku]

PostgreSQL Hacking Workshop - August 2024 summarized by Andreas Scherbaum @andreasscherbaum