Query finds posts with a 10 mile-radius of postal code. Response time is much too high
Number of rows in the PG Materialized Table: 520,000
Any help would be appreciated.
Query:
EXPLAIN ANALYZE SELECT * FROM post_list('Business','WptdA0GQgXuwcjEN9DINT', 37.12645,-113.49026)
Function:
CREATE OR REPLACE FUNCTION public.post_list(search_text text, alt_city_id text, lat numeric, long numeric)
RETURNS SETOF search_posts_sub_categories20
LANGUAGE sql
STABLE
AS $function$
select
category_name,
sub_category_name,
tags,
pa_weighted_tsv,
p_weighted_tsv,
category_id,
sub_category_id,
posted_by,
promotion_status,
post_id,
NULL::text AS rank,
zip_code_id,
alt_id,
detail,
price_range,
price_description,
title,
alt_city_id as not_used
from search_posts_sub_categories20,
plainto_tsquery('simple', search_text) AS q
WHERE
(((to_tsvector('simple', f_concat_ws(' ', category_name, sub_category_name))
@@ q )
or
(p_weighted_tsv @@ q ) or (pa_weighted_tsv @@ q ))
and ((promotion_status = 2 or promotion_status = 3)
or
((promotion_status = 4 or promotion_status = 1)
and (
((zip_code_id || alt_city_id
= ANY( (select array(SELECT DISTINCT (zc.id || ci.alt_id) FROM zip_codes as zc
join cities as ci on ci.id = zc.city_id
WHERE
ST_INTERSECTS(zc.geom,ST_BUFFER(ST_SETSRID(ST_POINT(long,lat),4326)::geography, 1609.34*10))
))::text[])
)
))
))) limit 100;
$function$
CALL Function:
CREATE OR REPLACE FUNCTION f_concat_ws(text, VARIADIC text[])
RETURNS text
LANGUAGE sql IMMUTABLE AS
'SELECT array_to_string($2, $1)';
INDEX
CREATE INDEX tbl_adr_fts_idx ON search_posts_sub_categories20 USING GIN (
to_tsvector('simple', f_concat_ws(' ', category_name, sub_category_name, tags::text, pa_weighted_tsv::text,
p_weighted_tsv::text)));
EXECUTION PLAN
Aggregate (cost=293745.15..293745.16 rows=1 width=32) -> Unique (cost=292594.18..293744.84 rows=24 width=40) -> Nested Loop Left Join (cost=292594.18..293744.78 rows=24 width=40) -> Limit (cost=292553.60..292553.72 rows=24 width=1380) -> Unique (cost=292553.60..292553.74 rows=28 width=1380) -> Sort (cost=292553.60..292553.67 rows=28 width=1380) Sort Key: _post_list.post_id -> Subquery Scan on _post_list (cost=11302.76..292552.93 rows=28 width=1380) Filter: (_post_list.promotion_status Limit (cost=11302.76..292551.89 rows=83 width=1380) InitPlan 7 (returns $31) -> Result (cost=11227.17..11227.18 rows=1 width=32) InitPlan 6 (returns $30) -> Unique (cost=11216.13..11227.17 rows=2208 width=32) -> Sort (cost=11216.13..11221.65 rows=2208 width=32) Sort Key: (((zc.id)::text || (ci.alt_id)::text)) -> Nested Loop (cost=0.29..11060.69 rows=2208 width=32) -> Seq Scan on zip_codes zc (cost=0.00..9930.67 rows=2208 width=16) Filter: (((geom)::geography && '0103000020E6100000010000002100000029D16E9BCA535CC09906D5A7A7904240628CAC80F5535CC0FD9A3CCC068D4240F543F7A090545CC0D8058B2D85894240EE59F7E995555CC05C8A4D3F45864240F89D013DFB565CC0D48FF2E6668342402E10AAD5B2585CC09448E744068142406F168AD2AB5A5CC0886185A33A7F424090F9C8DAD25C5CC06B1CC395157E424076A221DA125F5CC05C22BD4DA27D424037F385CC55615CC09FA11B31E57D424049681B9285635CC08ACF15AFDB7E4240C4941AC48C655CC09ED380597C8042408F27198357675CC04BD2F93FB782424003967337D4685CC0F710E68976854240C813E93CF4695CC09878C7499F88424010510C73AC6A5CC01F874E82128C42403F66F0ACF56A5CC07EC4B453AE8F4240A6CA75FBCC6A5CC0C03054464F93424022D7CFCE336A5CC0044D31A6D19642401E8045ED2F695CC08A9156E2129A4240FC5AC83ECB675CC0236480E2F29C424070C4B66E13665CC0DF64BC45559F4240A95CE66619645CC0073B4E7D22A142405329D3A8F0615CC0EEC068B848A242408E346A8BAE5F5CC040770E97BCA24240984B4065695D5CC043A3A89C79A242408FBE04AC375B5CC09DA4815D82A14240036B90122F595CC06A592D65E09F424004E406AF63575CC0F8AFE4D6A39D42407A121832E7555CC0E10BC8CBE29A4240244A9437C8545CC0C3FEB775B89742402DC544B711545CC0842DDA0F4494424029D16E9BCA535CC09906D5A7A7904240'::geography) AND (_st_distance((geom)::geography, '0103000020E6100000010000002100000029D16E9BCA535CC09906D5A7A7904240628CAC80F5535CC0FD9A3CCC068D4240F543F7A090545CC0D8058B2D85894240EE59F7E995555CC05C8A4D3F45864240F89D013DFB565CC0D48FF2E6668342402E10AAD5B2585CC09448E744068142406F168AD2AB5A5CC0886185A33A7F424090F9C8DAD25C5CC06B1CC395157E424076A221DA125F5CC05C22BD4DA27D424037F385CC55615CC09FA11B31E57D424049681B9285635CC08ACF15AFDB7E4240C4941AC48C655CC09ED380597C8042408F27198357675CC04BD2F93FB782424003967337D4685CC0F710E68976854240C813E93CF4695CC09878C7499F88424010510C73AC6A5CC01F874E82128C42403F66F0ACF56A5CC07EC4B453AE8F4240A6CA75FBCC6A5CC0C03054464F93424022D7CFCE336A5CC0044D31A6D19642401E8045ED2F695CC08A9156E2129A4240FC5AC83ECB675CC0236480E2F29C424070C4B66E13665CC0DF64BC45559F4240A95CE66619645CC0073B4E7D22A142405329D3A8F0615CC0EEC068B848A242408E346A8BAE5F5CC040770E97BCA24240984B4065695D5CC043A3A89C79A242408FBE04AC375B5CC09DA4815D82A14240036B90122F595CC06A592D65E09F424004E406AF63575CC0F8AFE4D6A39D42407A121832E7555CC0E10BC8CBE29A4240244A9437C8545CC0C3FEB775B89742402DC544B711545CC0842DDA0F4494424029D16E9BCA535CC09906D5A7A7904240'::geography, '0'::double precision, false) Index Scan using cities_pkey on cities ci (cost=0.29..0.50 rows=1 width=30) Index Cond: (id = zc.city_id) -> Bitmap Heap Scan on search_posts_sub_categories20 (cost=75.58..281324.72 rows=83 width=1380) Recheck Cond: ((to_tsvector('simple'::regconfig, f_concat_ws(' '::text, VARIADIC ARRAY[(category_name)::text, (sub_category_name)::text])) @@ '''business'''::tsquery) OR (p_weighted_tsv @@ '''business'''::tsquery) OR (pa_weighted_tsv @@ '''business'''::tsquery)) Filter: ((promotion_status = 2) OR (promotion_status = 3) OR (((promotion_status = 4) OR (promotion_status = 1)) AND (((zip_code_id)::text || (alt_city_id)::text) = ANY ($31)))) -> BitmapOr (cost=75.58..75.58 rows=8016 width=0) -> Bitmap Index Scan on tbl_adr_fts_idx (cost=0.00..28.84 rows=2672 width=0) Index Cond: (to_tsvector('simple'::regconfig, f_concat_ws(' '::text, VARIADIC ARRAY[(category_name)::text, (sub_category_name)::text])) @@ '''business'''::tsquery) -> Bitmap Index Scan on trgrm_ts_vec_idx (cost=0.00..23.34 rows=2672 width=0) Index Cond: (p_weighted_tsv @@ '''business'''::tsquery) -> Bitmap Index Scan on trgrm_ts_vec_idx (cost=0.00..23.34 rows=2672 width=0) Index Cond: (pa_weighted_tsv @@ '''business'''::tsquery) -> Nested Loop Left Join (cost=40.58..49.59 rows=1 width=32) -> Nested Loop Left Join (cost=1.00..7.73 rows=1 width=146) -> Limit (cost=0.42..2.64 rows=1 width=1057) -> Index Scan using users_pkey on users (cost=0.42..2.64 rows=1 width=1057) Index Cond: (id = _post_list.posted_by) -> Nested Loop Left Join (cost=0.58..5.07 rows=1 width=32) -> Limit (cost=0.29..2.51 rows=1 width=152) -> Index Scan using zip_codes_pkey on zip_codes (cost=0.29..2.51 rows=1 width=152) Index Cond: (id = users.zip_code_id) -> Subquery Scan on "_root.or.user.or.zip_code.or.city.base" (cost=0.29..2.53 rows=1 width=32) -> Limit (cost=0.29..2.51 rows=1 width=70) -> Index Scan using cities_pkey on cities (cost=0.29..2.51 rows=1 width=70) Index Cond: (id = zip_codes.city_id) SubPlan 1 -> Result (cost=0.00..0.01 rows=1 width=32) SubPlan 2 -> Result (cost=0.00..0.01 rows=1 width=32) -> Nested Loop Left Join (cost=39.57..41.84 rows=1 width=32) -> Limit (cost=0.14..2.36 rows=1 width=1250) -> Index Scan using files_pkey on files (cost=0.14..2.36 rows=1 width=1250) Index Cond: (id = users.avatar_file_id) -> Aggregate (cost=39.43..39.44 rows=1 width=32) -> Nested Loop Left Join (cost=0.14..39.34 rows=6 width=40) -> Seq Scan on post_attachments (cost=0.00..25.00 rows=6 width=16) Filter: (files.id = file_id) -> Subquery Scan on "_root.or.user.or.avatar.ar.avatar.post_attachments.or.file.base" (cost=0.14..2.38 rows=1 width=32) -> Limit (cost=0.14..2.36 rows=1 width=1250) -> Index Scan using files_pkey on files files_1 (cost=0.14..2.36 rows=1 width=1250) Index Cond: (id = post_attachments.file_id) SubPlan 8 -> Result (cost=0.00..0.01 rows=1 width=32) SubPlan 9 -> Result (cost=0.00..0.01 rows=1 width=32) SubPlan 3 -> Result (cost=0.00..0.01 rows=1 width=32) SubPlan 4 -> Result (cost=0.00..0.01 rows=1 width=32) SubPlan 5 -> Result (cost=0.00..0.01 rows=1 width=32) JIT: Functions: 118 Options: Inlining false, Optimization false, Expressions true, Deforming true
IN (SELECT ...)
clause in the function that has to be evaluated for each of the estimated 8000 rows. Try to rewrite that as a join or anEXISTS
clause.