0

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
Improve this question
7
  • The function definition is incomplete, the execution plan is incomplete (the last nested loop join has no second table), and the query you show is not SQL. Please remedy these shortcomings. Anyway, the cause for the long run time is clearly the 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 an EXISTS clause.
    – Laurenz Albe
    Aug 31, 2022 at 4:15
  • Thanks, I have revised the query. Will look into EXISTS.
    – Guy
    Aug 31, 2022 at 13:32
  • Please preserve the indenting of your query plan.
    – jjanes
    Aug 31, 2022 at 13:56
  • Your work_mem is too low, as shown both by lossy=39143 and by (I think) the subplan being iterated rather than hashed.
    – jjanes
    Aug 31, 2022 at 14:09
  • Given how common 'Business' is as a category or subcategory, it should probably be blacklisted as a search term.
    – jjanes
    Aug 31, 2022 at 14:11

1 Answer 1

Reset to default
0

  1. Using Laurenz Albe suggestions, sub-query was changed.

    (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[])

  2. Added the following indexes:

    CREATE INDEX promotion_status_idx ON public.search_posts_sub_categories20 USING btree (promotion_status);

    CREATE INDEX zip_alt_idx ON public.search_posts_sub_categories20 USING btree (((zip_code_id || (alt_city_id)::text)) varchar_pattern_ops);

3 Execution Plan looks much better:

    Aggregate  (cost=11810.19..11810.20 rows=1 width=32)
  ->  Unique  (cost=11808.74..11809.15 rows=83 width=1380)
        ->  Sort  (cost=11808.74..11808.94 rows=83 width=1380)
              Sort Key: search_posts_sub_categories20.post_id
              ->  Limit  (cost=11636.30..11774.51 rows=83 width=1380)
                    InitPlan 2 (returns $2)
                      ->  Result  (cost=11227.17..11227.18 rows=1 width=32)
                            InitPlan 1 (returns $1)
                              ->  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=409.12..547.33 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)) AND ((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 ($2)))))
                          ->  BitmapAnd  (cost=409.12..409.12 rows=84 width=0)
                                ->  BitmapOr  (cost=79.98..79.98 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 p_weighted_tsv_idx  (cost=0.00..27.74 rows=2672 width=0)
                                            Index Cond: (p_weighted_tsv @@ '''business'''::tsquery)
                                      ->  Bitmap Index Scan on pa_weighted_tsv_idx  (cost=0.00..23.34 rows=2672 width=0)
                                            Index Cond: (pa_weighted_tsv @@ '''business'''::tsquery)
                                ->  BitmapOr  (cost=328.89..328.89 rows=5605 width=0)
                                      ->  Bitmap Index Scan on promotion_status_idx  (cost=0.00..23.77 rows=2672 width=0)
                                            Index Cond: (promotion_status = 2)
                                      ->  Bitmap Index Scan on promotion_status_idx  (cost=0.00..23.77 rows=2672 width=0)
                                            Index Cond: (promotion_status = 3)
                                      ->  BitmapAnd  (cost=281.07..281.07 rows=261 width=0)
                                            ->  BitmapOr  (cost=47.57..47.57 rows=5344 width=0)
                                                  ->  Bitmap Index Scan on promotion_status_idx  (cost=0.00..23.77 rows=2672 width=0)
                                                        Index Cond: (promotion_status = 4)
                                                  ->  Bitmap Index Scan on promotion_status_idx  (cost=0.00..23.77 rows=2672 width=0)
                                                        Index Cond: (promotion_status = 1)
                                            ->  Bitmap Index Scan on zip_alt_idx  (cost=0.00..233.22 rows=26126 width=0)
                                                  Index Cond: (((zip_code_id)::text || (alt_city_id)::text) = ANY ($2))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.