Optimal way to get a total count of rows in a paged query in Postgres?
2
votes
1
answer
5911
views
I need to improve the performance of a paged query for customer orders in a Type2 Postgres db (always insert a new record with a new ID, the newest ID is the current version of the record). Changing away from Type2 is not an option at this time. The query I have is two queries with the same CTE in both:
WITH customer_orders AS (
select id, order_id, customer_id,
"name", country, state, county, source_system,
is_deleted, created_at, updated_at, deleted_at,
created_by, updated_by, deleted_by,
rank() over (partition by order_id order by id desc) as entity_rank
from orders WHERE customer_id = $1 and is_deleted= $2
)
SELECT * FROM customer_orders where entity_rank = 1 ORDER BY id DESC LIMIT $3 OFFSET $4;
WITH customer_orders AS (
select id, order_id, customer_id,
"name", country, state, county, source_system,
is_deleted, created_at, updated_at, deleted_at,
created_by, updated_by, deleted_by,
rank() over (partition by order_id order by id desc) as entity_rank
from orders WHERE customer_id = $1 and is_deleted= $2
)
SELECT count(id) FROM customer_orders where entity_rank = 1;
But I wonder if there's a better way to do this, can I select from the CTE twice, once for the paging (limit + offset) and once for the total number of records? I'll be running this as two separate queries from a Node process. It seems like it should be doable in one query but I can't get it.
Indexes: id (PK), order_id, customer_id, is_deleted (1 on each of those)
Asked by jcollum
(229 rep)
Aug 18, 2023, 11:26 PM
Last activity: Aug 29, 2023, 08:07 PM
Last activity: Aug 29, 2023, 08:07 PM