Single PostgreSQL Query Can Handle Pagination but Risks Inefficient Execution Plans

A SQL technique allows developers to write one prepared statement in PostgreSQL that handles both first-page and subsequent-page pagination, using a NULL-check condition to toggle filtering. The approach uses a WHERE clause structured as '$1::timestamptz IS NULL OR timestamp > $1', eliminating the need for two separate queries as typically required in MongoDB implementations. When PostgreSQL uses a custom plan, it detects the NULL check at parse time and applies an efficient index scan, reading only the required rows. However, when the database falls back to a generic plan — as can happen after repeated executions — the index condition is dropped, causing a far slower full index scan across thousands of rows. Developers are advised to be aware of this trade-off when relying on generic prepared statements for performance-critical pagination queries.