Slow Bitmap Index Scan

Hello all,

We recently moved our production database systems from a 9.4 running on a self-managed EC2 instance to 9.6.10 on Amazon’s AWS (same RAM, CPU).  After the move, we’re finding that certain queries that we run against a GIN full-text index have some occasionally very slow executions and I’m struggling to figure out what to do about it.   I would be very grateful for any ideas!

Thanks,

Scott

The setup we have is a 32-core, 244 GB RAM primary with a same-sized read replica.  The queries are running off the replica, but performance is roughly the same between the master and the replica.

Here’s a query that’s performing badly:

SELECT ls.location AS locationId  FROM location_search ls  WHERE ls.client = 83  AND search_field_tsvector @@ to_tsquery('9000:* &Smith''s:* &Mill:*')  AND ls.favorite = TRUE  LIMIT 1

The mean time for this query (and others like it) is about 900ms, but the std deviation is over 1000ms and the max is 11000ms.

The explain looks like this:

Limit  (cost=1516.25..1520.52 rows=1 width=223) (actual time=4506.482..4506.482 rows=0 loops=1)

  Buffers: shared hit=9073

  ->  Bitmap Heap Scan on location_search ls  (cost=1516.25..1520.52 rows=1 width=223) (actual time=4506.480..4506.480 rows=0 loops=1)

        Recheck Cond: (search_field_tsvector @@ to_tsquery('9000:* &Smith''s:* &Mill:*'::text))

        Filter: (favorite AND (client = 83))

        Rows Removed by Filter: 8

        Heap Blocks: exact=12

        Buffers: shared hit=9073

        ->  Bitmap Index Scan on location_search_tsvector_idx  (cost=0.00..1516.25 rows=1 width=0) (actual time=4506.450..4506.450 rows=12 loops=1)

              Index Cond: (search_field_tsvector @@ to_tsquery('9000:* &Smith''s:* &Mill:*'::text))

              Buffers: shared hit=9061

Planning time: 0.240 ms

Execution time: 4509.995 ms

The table has about 30 million rows in it.   The table and index definition are:

CREATE TABLE public.location_search

(

    id bigint NOT NULL DEFAULT nextval('location_search_id_seq'::regclass),

    person_location bigint,

    person bigint,

    client_location bigint,

    client bigint,

    location bigint,

    org_unit_id bigint,

    latitude numeric(10,7),

    longitude numeric(10,7),

    geofence numeric(10,7),

    address_line_one text COLLATE pg_catalog."default",

    address_line_two text COLLATE pg_catalog."default",

    city text COLLATE pg_catalog."default",

    state text COLLATE pg_catalog."default",

    postal_code text COLLATE pg_catalog."default",

    country text COLLATE pg_catalog."default",

    full_address text COLLATE pg_catalog."default",

    is_google_verified boolean,

    address_source text COLLATE pg_catalog."default",

    active boolean,

    name character varying(255) COLLATE pg_catalog."default",

    external_client_location_id character varying(500) COLLATE pg_catalog."default",

    custom_field_values hstore,

    location_tags hstore,

    legacy_location_id bigint,

    favorite boolean,

    search_field_tsvector tsvector

)

WITH (

    OIDS = FALSE

)

TABLESPACE pg_default;

CREATE INDEX location_search_tsvector_idx

    ON public.location_search USING gin

    (search_field_tsvector)

    TABLESPACE pg_default;

Right now the output of pgstatginindex is this:

version pending_pages pending_tuples

2              214         9983

Lastly, here are some of the relevant config entries:

autovacuum on

autovacuum_analyze_scale_factor 0

autovacuum_analyze_threshold 50

autovacuum_freeze_max_age 400000000

autovacuum_max_workers 3

autovacuum_multixact_freeze_max_age 400000000

autovacuum_naptime 30s

autovacuum_vacuum_cost_delay 20ms

autovacuum_vacuum_cost_limit    -1

autovacuum_vacuum_scale_factor 0

autovacuum_vacuum_threshold 50

cpu_index_tuple_cost 0

cpu_operator_cost 0

cpu_tuple_cost 0

cursor_tuple_fraction 0

effective_cache_size 125777784kB

effective_io_concurrency 1

gin_fuzzy_search_limit 0

gin_pending_list_limit 4MB

maintenance_work_mem 4027MB

seq_page_cost 1

shared_buffers 62888888kB

work_mem 200000kB

SCOTT RANKIN
VP, Technology

Motus, LLC
Two Financial Center, 60 South Street, Boston, MA 02111 
617.467.1900 (O) | srankin@motus.com

Follow us on LinkedIn | Visit us at motus.com