All Subscriptions' item query takes 0.3-0.9 s for about 14,000 items #241
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
GET /api/entries (All Subscriptions) spends 300-900 ms in its entries_in step (trace 264c47a46ac870aa410379085cd720c8: 302 of 328 ms), and the feed list's per-feed count 366 ms, for an account whose 147 feeds hold about 14,000 items. Find out with EXPLAIN on the load test's Postgres (tests/load) why, and fix the query or its index.
Measured on the load test's Postgres (same server as production's), data scaled to production's shape: All Subscriptions' query 31.8 ms (top-N heapsort over 14,700 index-only rows, 6,027 heap fetches), the feed list's count 12 ms. Production: 300-900 ms and 366 ms. So the plan and indexes are fine; the difference is production's own: table bloat or a stale visibility map (heap fetches), statistics, or a cold cache on a server shared with other databases. Next: EXPLAIN (ANALYZE, BUFFERS) of the same two queries on production, read only, with the owner's go-ahead.
Fixed in
62a3241, deployed (ipx 1.2.1-dev, ipx-directory 1.1.2-dev). Not the queries: on production, read only, All Subscriptions 31 ms, unread oldest-first 30 ms, the feed list's count 5 ms, generic plans 16 ms. The time was new Postgres connections, 80-330 ms each, opened when a page load asked for more than were idle (four of six slow loads came as the pool grew). The daemon now keeps all 20 open and the directory all 10; ipx's commands and the tests open as they need. 20 /api/feeds at once: median 414 ms before (pool 8 to 20), 197 ms after (pool stays 20).