How does PostgreSQL actually work?
Peter Eisentraut
[email protected]
@petereisentraut
Slide 2
Slide 2 text
2ndQuadrant
>10 years of PostgreSQL services
>50 staff in >20 countries
offers 24x7 production support and consulting for PostgreSQL
one of the leading code contributors
Slide 3
Slide 3 text
2ndQuadrant PostgreSQL Conference
http://www.2qpgconf.com/
November 6 & 7 New York
November 9 Chicago
Slide 4
Slide 4 text
“From Query to Disk and Back
Again”
Slide 5
Slide 5 text
query
SELECT date_trunc('month', orderdate), sum(netamount)
FROM customers JOIN orders USING (customerid)
WHERE customers.age BETWEEN 18 AND 35
GROUP BY date_trunc('month', orderdate)
ORDER BY sum(netamount) DESC;
psql
while (true)
{
char *input = readline("=> ");
if (strcmp(input, "\q") == 0)
exit(0);
PGresult = PQexec(conn, input);
/* print result */
}
Slide 8
Slide 8 text
psql/libpq
$ otool -L /usr/local/opt/postgresql@10/bin/psql
/usr/local/opt/postgresql@10/bin/psql:
/usr/local/Cellar/postgresql@10/10.0/lib/libpq.5.dylib (compatibility version 5.0.0, current version 5.1
/usr/local/opt/gettext/lib/libintl.8.dylib (compatibility version 10.0.0, current version 10.5.0)
/usr/local/opt/readline/lib/libreadline.7.dylib (compatibility version 7.0.0, current version 7.0.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1238.50.2)
parse analysis
the rest of src/backend/parser/
additional syntax checks
check that tables, columns, etc. exist
check that types match
system catalogs (pg_class, pg_type, ...)
results in modified parse tree
Slide 17
Slide 17 text
parse analysis
WHERE customers.age BETWEEN 18 AND 35
select oid from pg_type where typname = 'int2';
select oprcode from pg_operator
where oprname = '>=' and oprleft = 21 and oprright = 21;
select prosrc from pg_proc where proname = 'int2ge';
Datum
int2ge(PG_FUNCTION_ARGS)
{
int16 arg1 = PG_GETARG_INT16(0);
int16 arg2 = PG_GETARG_INT16(1);
PG_RETURN_BOOL(arg1 >= arg2);
}
Slide 18
Slide 18 text
rewriting
src/backend/rewrite/
expand views, rules
results in parse tree
Slide 19
Slide 19 text
planning/optimizing
src/backend/optimizer/
parse tree → execution plan