Suppose I have the following table of cafes & restaurants in the USA:
CREATE TABLE shops (
id serial PRIMARY KEY
name text NOT NULL,
category text, -- Could be anything the owner wants (eg. cafe-bar, italian etc)
services text[], -- Preselected for the owner to choose (eg. 'beverages', 'alcohol', 'snacks' etc)
city text NOT NULL, -- Again, user specified (eg. Seattle)
state text NOT NULL CHECK (state in ('NY', 'CA', 'AZ', 'WA',...),
rating real CHECK (rating IS NULL OR rating BETWEEN 1 AND 10) -- Updated from external table
)
I need to select shops in the following way:
SELECT *, COUNT(*) OVER() AS filter_results
FROM shops
WHERE state IN ('NY', 'CA')
AND services && '{"snacks", "beverages"}'
ORDER BY rating DESC NULLS LAST
LIMIT 100 OFFSET 500 -- OFFSET can be anything >= 0, just specifying one as an example
I tested this in a similar schema and I got execution times of about 1300 ms from a table of more than a million records.
When I removed the COUNT(*) OVER() AS filter_results from the query, it took only about 1.2 ms - over a thousand times faster.
Running the following query to get the count separately took about 400 ms:
SELECT COUNT(*)
FROM shops
WHERE state IN ('NY', 'CA')
AND services && '{"snacks", "beverages"}'
I have the following indexes:
CREATE INDEX idx_shops_state ON shops (state);
CREATE INDEX idx_shops_services ON shops USING GIN (services);
CREATE INDEX idx_shops_rating ON shops (rating DESC NULLS LAST);
The fact that the query only takes 1.2 ms without the window function means that the indexes work just fine.
The filter result count is required for pagination. I've thought about precomputing all the combinations, but they are just too many.
Is there any way to optimize the window function and get the filter result count in less than 10 ms? We are talking about a million record table.
Here's what I got by running EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) before the query (LIMIT 200, NO OFFSET):
WITH WINDOW FUNCTION:
"Limit (cost=0.43..3933.73 rows=200 width=736) (actual time=1593.568..1593.697 rows=200 loops=1)"
" Output: id, name, category, services, city, state, rating, (count(*) OVER (?))"
" Buffers: shared hit=5 read=96961, temp read=2706 written=3003"
" -> WindowAgg (cost=0.43..749077.54 rows=38089 width=736) (actual time=1593.567..1593.688 rows=200 loops=1)"
" Output: id, name, category, services, city, state, rating, (count(*) OVER (?))"
" Buffers: shared hit=5 read=96961, temp read=2706 written=3003"
" -> Index Scan using idx_shops_rating on public.shops (cost=0.43..748601.43 rows=38089 width=728) (actual time=0.054..1473.201 rows=38327 loops=1)"
" Output: id, name, category, services, city, state, rating"
" Filter: ((shops.state = ANY ('{NY,CA}'::text[])) AND ('{snacks,beverages}'::text[] && shops.services))"
" Rows Removed by Filter: 1051796"
" Buffers: shared hit=5 read=96961"
"Planning:"
" Buffers: shared read=1"
"Planning Time: 0.257 ms"
"Execution Time: 1596.514 ms"
WITHOUT WINDOW FUNCTION:
"Limit (cost=0.43..3931.23 rows=200 width=728) (actual time=0.014..1.005 rows=200 loops=1)"
" Output: id, name, category, services, city, state, rating"
" Buffers: shared hit=405"
" -> Index Scan using idx_shops_rating on public.shops (cost=0.43..748601.43 rows=38089 width=728) (actual time=0.013..0.996 rows=200 loops=1)"
" Output: id, name, category, services, city, state, rating"
" Filter: ((shops.state = ANY ('{NY,CA}'::text[])) AND ('{snacks,beverages}'::text[] && shops.services))"
" Rows Removed by Filter: 979"
" Buffers: shared hit=405"
"Planning:"
" Buffers: shared hit=1"
"Planning Time: 0.230 ms"
"Execution Time: 1.026 ms"
After experimenting for a while, I realized that with a an offset of 50 or more the execution time falls from 1250 ms to about 70 ms. Maybe this can help, I don't know...