Andrew Atkinson andyatkinson

Timeline:

Oct 3: Learning about this issue
Tweet: https://x.com/andatki/status/1841915460062769187
Reviewing commit: https://github.com/postgres/postgres/commit/62ddf7ee9a399e0b9624412fc482ed7365e38958
Summarizing thoughts below

Goal: Understand WHY to ANALYZE ONLY on a root partition table.

The current version (17) documentation seems to be wrong. It says only the root table is analyzed, but in the commit message David Rowley describes the partitions of the root table are also analyzed when ANALYZE runs on the root.

:quit
:qa
exit
exit!
quit
.quit
process.exit()
\q

Keybase proof

I hereby claim:

I am andyatkinson on github.
I am andatki (https://keybase.io/andatki) on keybase.
I have a public key whose fingerprint is 2BC1 EF01 7DEA 0698 523F 8A3C A6D9 3792 EFC7 54EE

To claim this, I am signing this object:

issues xfter a restart where postgres is running, but not with launchctl, not sure why stop manually:

pg_ctl -D /usr/local/var/postgres stop -s -m fast

restart with launchctl:

launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

On Mavericks, my laptop had issues on my home wi-fi, an old WRT54G Linksys router.

Troubleshooting:

Tips:

change DNS servers to google DNS 8.8.8.8

	psql> create table t(a uuid, b text, c char(36));
	CREATE TABLE

	psql> insert into t values (gen_random_uuid(), 'some amount of text', 'some amount of text');
	INSERT 0 1

	psql> select pg_column_size(a), pg_column_size(b), pg_column_size(c) from t;
	pg_column_size \| pg_column_size \| pg_column_size
	----------------+----------------+----------------
	16 \| 20 \| 37

	#
	# CTE support in Active Record was added to Ruby on Rails 7.1
	# https://blog.appsignal.com/2023/02/15/whats-new-in-rails-7-1.html#
	#
	# The example below produces an equivalent query to the SQL version below.
	#
	# Rails app: https://github.com/andyatkinson/rideshare
	#
	irb(main):395* Driver.with(drivers_recent_completed_trip: Driver.select('users.id AS driver_id').joins(trips: :trip_request).
	irb(main):396* where.not(trips: {completed_at: nil}).

	create table t (id serial, created_at timestamptz) partition by range (created_at);
	create table t_202309 partition of t for values from ('2023-09-01') TO ('2023-10-01');
	create table t_202310 partition of t for values from ('2023-10-01') TO ('2023-11-01');


	postgres@[local]:5432 testing# \d t
	Partitioned table "public.t"
	Column \| Type \| Collation \| Nullable \| Default
	------------+--------------------------+-----------+----------+-------------------------------
	id \| integer \| \| not null \| nextval('t_id_seq'::regclass)

	# Prereq: auto_explain added to shared_preload_libraries

	# Compute the Query Identifier
	compute_query_id = on

	# Logging duration
	log_duration = on

	# Logging Query Identifier (%Q)
	log_line_prefix = 'pid=%p query_id=%Q: '

	# example config from a nginx + unicorn + capistrano deploy to a VPS
	# "app" is deployed to /var/www/app
	# replace "app" with the app name

	# The rule of thumb is to use 1 worker per processor core available,.
	# however since we'll be hosting many apps on this server,.
	# we need to take a less aggressive approach
	worker_processes 2

	# We deploy with capistrano, so "current" links to root dir of current release