- I have 3 tables and a materialized view
resource_categories contains all category names and metadata
create table if not exists resource_categories (
category_id INT,
title VARCHAR(255),
content TEXT,
icon VARCHAR(50)
);
resources contains all resources under each category
create table if not exists resources (
resource_id INT,
title VARCHAR(255),
content TEXT,
link VARCHAR(1000),
category_id INT REFERENCES resource_categories(resource_id),
icon VARCHAR(255),
created_at DATE,
updated_at DATE
);
resource_votes contains user liking a resource or disliking it
create table if not exists resource_votes (
resource_id INT REFERENCES resources(resource_id),
user_id INT,
vote BOOLEAN
);
resource_votes_aggregate a materialized view with likes per resource_id
- I want to execute the following queries
- Find all resources sorted in descending order of likes (find most liked resources)
- Find all resources sorted in descending alphabetical order of their titles
- I want to use SEEK / KEYSET pagination for efficiency
Query to find resources in descending order of their likes page 1
SELECT
r.resource_id,
title,
COALESCE(likes, 0) AS likes
FROM
resources r
LEFT JOIN
resource_votes_aggregate a
ON r.resource_id = a.resource_id
ORDER BY
likes DESC,
resource_id DESC LIMIT 5;
Execution Plan
QUERY PLAN
Limit (cost=74.50..74.52 rows=5 width=157) (actual time=0.058..0.060 rows=5 loops=1)
-> Sort (cost=74.50..76.80 rows=918 width=157) (actual time=0.058..0.058 rows=5 loops=1)
Sort Key: (COALESCE(a.likes, '0'::bigint)) DESC, r.resource_id DESC
Sort Method: top-N heapsort Memory: 25kB
-> Hash Right Join (cost=12.03..59.25 rows=918 width=157) (actual time=0.032..0.046 rows=50 loops=1)
Hash Cond: (a.resource_id = r.resource_id)
-> Seq Scan on resource_votes_aggregate a (cost=0.00..30.40 rows=2040 width=12) (actual time=0.001..0.004 rows=38 loops=1)
-> Hash (cost=10.90..10.90 rows=90 width=149) (actual time=0.021..0.021 rows=50 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Seq Scan on resources r (cost=0.00..10.90 rows=90 width=149) (actual time=0.003..0.010 rows=50 loops=1)
Planning Time: 0.050 ms
Execution Time: 0.075 ms
This and all the other queries generate a sequential scan which beats the entire purpose of seek pagination
- How do I fix this. Help is super appreciated