I have this application that is in Next.js for the frontend/backend, Supabase for the database.
I’m running this query on my reviews table:
SELECT review_image, review_description, review_title, rating_stars, review_author, helpful, reviewed_at
FROM reviews
WHERE type = 'Car'
ORDER BY sku ASC, helpful DESC NULLS LAST
LIMIT 8 OFFSET 0;
This is the index I’ve applied to it:
CREATE INDEX idx_reviews_type_sku_helpful ON public.reviews USING btree (type, sku, helpful DESC);
This is the query plan I get when I run it:
| QUERY PLAN |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Limit (cost=21.02..21.82 rows=8 width=396) (actual time=1.214..1.215 rows=8 loops=1) |
| -> Incremental Sort (cost=21.02..100710.01 rows=1000658 width=396) (actual time=1.213..1.213 rows=8 loops=1) |
| Sort Key: sku, helpful DESC NULLS LAST |
| Presorted Key: sku |
| Full-sort Groups: 1 Sort Method: top-N heapsort Average Memory: 27kB Peak Memory: 27kB |
| Pre-sorted Groups: 1 Sort Method: top-N heapsort Average Memory: 32kB Peak Memory: 32kB |
| -> Index Only Scan using idx_reviews_type_sku_helpful on reviews (cost=0.55..56846.32 rows=1000658 width=396) (actual time=0.081..1.062 rows=301 loops=1) |
| Index Cond: (type = 'Car'::text) |
| Heap Fetches: 185 |
| Planning Time: 22.106 ms |
| Execution Time: 1.847 ms
This is the query plan after I drop the index and re-apply it.
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Limit (cost=21.22..22.06 rows=8 width=396) (actual time=147.749..147.751 rows=8 loops=1) |
| -> Incremental Sort (cost=21.22..101625.43 rows=965343 width=396) (actual time=147.747..147.748 rows=8 loops=1) |
| Sort Key: sku, helpful DESC NULLS LAST |
| Presorted Key: sku |
| Full-sort Groups: 1 Sort Method: top-N heapsort Average Memory: 27kB Peak Memory: 27kB |
| Pre-sorted Groups: 1 Sort Method: top-N heapsort Average Memory: 32kB Peak Memory: 32kB |
| -> Index Scan using idx_reviews_type_sku_helpful on reviews (cost=0.43..59306.77 rows=965343 width=396) (actual time=1.190..147.176 rows=301 loops=1) |
| Index Cond: (type = 'Car'::text) |
| Planning Time: 21.310 ms |
| Execution Time: 147.856 ms
This is the query plan after I use VACUUM
| QUERY PLAN |
| ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Limit (cost=21.26..22.10 rows=8 width=399) (actual time=114.537..114.540 rows=8 loops=1) |
| -> Incremental Sort (cost=21.26..101756.74 rows=959284 width=399) (actual time=114.535..114.537 rows=8 loops=1) |
| Sort Key: sku, helpful DESC NULLS LAST |
| Presorted Key: sku |
| Full-sort Groups: 1 Sort Method: top-N heapsort Average Memory: 27kB Peak Memory: 27kB |
| Pre-sorted Groups: 1 Sort Method: top-N heapsort Average Memory: 32kB Peak Memory: 32kB |
| -> Index Scan using idx_review_type_sku_helpful on reviews (cost=0.43..59703.11 rows=959284 width=399) (actual time=1.563..113.774 rows=301 loops=1) |
| Index Cond: (type = 'Car'::text) |
| Planning Time: 3.248 ms |
| Execution Time: 114.642 ms
Is this a problem on Next.js side? I’m running the same query here:
export default async function CarPage() {
try {
reviewData = getProductReviewsByPage(
{ productType: "Car" },
{
pagination: {
page: 0,
limit: 8,
},
sort: [
{ field: 'sku', order: 'asc' },
{ field: 'helpful', order: 'desc', nullsFirst: false },
],
}
)
} catch ...
return (
<Car review={review}/>
)
}
I get this error message:
[getProductReviewsByPage] caught: {
code: '57014',
details: null,
hint: null,
message: 'canceling statement due to statement timeout'
}
Here’s a snippet of the Supabase javascript:
let fetch = supabaseDatabaseClient
.from(PRODUCT_REVIEWS_TABLE)
.select(
'review_image,review_description,review_title,rating_stars,review_author,helpful,reviewed_at'
)
.eq('type', "Car")
.range(from, to)
.order(...)
Is there something I can do optimize my query more?
Or is this something that’s on Next.js side that I need to adjust?
The table I’m querying against has 1mill records.
Some things I’ve thought about:
- Clustering
- Partitioning the table
- Try to increase timeout?
- Load the reviews after the page loads and add retries