How would you optimize a query that frequently joins a massive transaction table with a smaller, highly dynamic user metadata table on Patreon?
Use the supplied PostgreSQL schema to write the representative query and explain the indexing, filtering, join, and execution-plan decisions that make it efficient. Select the latest metadata available by the reporting cutoff, include only active creators and settled January 2025 transactions, and preserve the required result contract.
report_month, country, membership_tier, transaction_count, and total_amount.| Column | Type | Description |
|---|---|---|
| transaction_idPK | BIGINT | Unique transaction identifier |
| user_id | BIGINT | Patreon user associated with the transaction |
| occurred_at | TIMESTAMP | Transaction timestamp |
| status | VARCHAR(20) | Transaction processing status |
| amount | NUMERIC(12,2) | Transaction amount in reporting currency |
| Column | Type | Description |
|---|---|---|
| metadata_idPK | BIGINT | Unique metadata version identifier |
| user_id | BIGINT | Patreon user represented by this metadata version |
| effective_at | TIMESTAMP | Time at which this metadata version became effective |
| country | VARCHAR(2) | ISO country code |
| membership_tier | VARCHAR(30) | Current membership tier label |
| is_creator | BOOLEAN | Whether the user is a creator |