mirror of
https://github.com/knadh/listmonk.git
synced 2026-01-18 06:55:52 +08:00
cea65c009d
This has been a hair-pulling rabbit hole of an issue. #1931 and others. When the `next-campaign-subscribers` query that fetches $n subscribers per batch for a campaign returns no results, the manager assumes that the campaign is done and marks as finished. Marathon debugging revealed fundamental flaws in qyery's logic that would incorrectly return 0 rows under certain conditions. - Based on the "layout" of subscribers for eg: a series of blocklisted subscribers between confirmed subscribers. A series of unconfirmed subscribers in a batch belonging to a double opt-in list. - Bulk import blocklisting users, but not marking their subscriptions as 'unsubscribed'. - Conditions spread across multiple CTEs resulted in returning an arbitrary number of rows and $N per batch as the selected $N rows would get filtered out elsewhere, possibly even becoming 0. After fixing this and testing it on our prod instance that has 15 million subscribers and ~70 million subscriptions in the `subscriber_lists` table, ended up discovered significant inefficiences in Postgres query planning. When `subscriber_lists` and campaign list IDs are joined dynamically (CTE or ANY() or any kind of JOIN that involves) a query, the Postgres query planner is unable to use the right indexes. After testing dozens of approaches, discovered that statically passing the values to join on (hardcoding or passing via parametrized $1 vars), the query uses the right indexes. The difference is staggering. For the particular scenario on our large prod DB to pull a batch, ~15 seconds vs. ~50ms, a whopping 300x improvement! This patch splits `next-campaign-subscribers` into two separate queries, one which fetches campaign metadata and list_ids, whose values are then passed statically to the next query to fetch subscribers by batch. In addition, it fixes and refactors broken filtering and counting logic in `create-campaign` and `next-campaign` queries. Closes #1931, #1993, #1986. |
||
|---|---|---|
| .. | ||
| admin.go | ||
| archive.go | ||
| auth.go | ||
| bounce.go | ||
| campaigns.go | ||
| events.go | ||
| handlers.go | ||
| i18n.go | ||
| import.go | ||
| init.go | ||
| install.go | ||
| lists.go | ||
| main.go | ||
| maintenance.go | ||
| manager_store.go | ||
| media.go | ||
| notifications.go | ||
| public.go | ||
| roles.go | ||
| settings.go | ||
| subscribers.go | ||
| templates.go | ||
| tx.go | ||
| updates.go | ||
| upgrade.go | ||
| users.go | ||
| utils.go | ||