Debian Question posted in
The oficial documentation can be found here.

Debian – Weird postgres planner choice when using pgroonga text search, causing queries to be slow

I’ve noticed that some of my queries using pgroonga text search and involving multiple tables, are slow. Looking into the problem I’ve noticed that for some unknown reasons, postgres planner, tends to use improper indexes, instead of the proonga ones, where appropriate.

for example, the following query is executed in about 15 seconds, instead of milliseconds if pgroonga index would have been chosen:

explain(analyze, verbose, buffers, settings) 
select * from products_locations
where product_id in (SELECT product_id FROM search."en" WHERE 1=1 AND text &@ 'varza');

output:

Gather  (cost=1105.97..20062891.30 rows=289236 width=356) (actual time=15673.818..15860.592 rows=0 loops=1)
  Output: products_locations.id, products_locations.product_id, products_locations.store_id, products_locations.aisle, products_locations.latitude, products_locations.longitude, products_locations.username, products_locations.creation_time, products_locations.update_time, products_locations.base_price, products_locations.quantity, products_locations.currency, products_locations.fiscal_region, products_locations.is_online, products_locations.online_location, products_locations.covered_regions, products_locations.price_100, products_locations.commit_id
  Workers Planned: 2
  Workers Launched: 2
  Buffers: shared hit=3233875
  ->  Nested Loop  (cost=105.97..20032967.70 rows=120515 width=356) (actual time=15610.381..15610.383 rows=0 loops=3)
        Output: products_locations.id, products_locations.product_id, products_locations.store_id, products_locations.aisle, products_locations.latitude, products_locations.longitude, products_locations.username, products_locations.creation_time, products_locations.update_time, products_locations.base_price, products_locations.quantity, products_locations.currency, products_locations.fiscal_region, products_locations.is_online, products_locations.online_location, products_locations.covered_regions, products_locations.price_100, products_locations.commit_id
        Buffers: shared hit=3233875
        Worker 0:  actual time=15580.304..15580.306 rows=0 loops=1
          JIT:
            Functions: 8
            Options: Inlining true, Optimization true, Expressions true, Deforming true
            Timing: Generation 0.741 ms, Inlining 72.001 ms, Optimization 64.946 ms, Emission 37.857 ms, Total 175.545 ms
          Buffers: shared hit=1060335
        Worker 1:  actual time=15579.476..15579.478 rows=0 loops=1
          JIT:
            Functions: 8
            Options: Inlining true, Optimization true, Expressions true, Deforming true
            Timing: Generation 0.883 ms, Inlining 75.280 ms, Optimization 74.995 ms, Emission 39.002 ms, Total 190.161 ms
          Buffers: shared hit=1088833
        ->  Parallel Index Scan using search_en_product_id_locale_key on search.en  (cost=0.43..4596035.99 rows=949 width=16) (actual time=13744.720..15610.351 rows=0 loops=3)
              Output: en.id, en.product_id, en.text, en.username, en.creation_time, en.update_time
              Filter: (en.text &@ 'varza'::text)
              Rows Removed by Filter: 1070204
              Buffers: shared hit=3233870
              Worker 0:  actual time=9983.327..15580.217 rows=1 loops=1
                Buffers: shared hit=1060330
              Worker 1:  actual time=15579.475..15579.475 rows=0 loops=1
                Buffers: shared hit=1088833
        ->  Bitmap Heap Scan on public.products_locations  (cost=105.54..16186.01 rows=8051 width=356) (actual time=0.074..0.075 rows=0 loops=1)
              Output: products_locations.id, products_locations.product_id, products_locations.store_id, products_locations.aisle, products_locations.latitude, products_locations.longitude, products_locations.username, products_locations.creation_time, products_locations.update_time, products_locations.base_price, products_locations.quantity, products_locations.currency, products_locations.fiscal_region, products_locations.is_online, products_locations.online_location, products_locations.covered_regions, products_locations.price_100, products_locations.commit_id
              Recheck Cond: (products_locations.product_id = en.product_id)
              Buffers: shared hit=5
              Worker 0:  actual time=0.074..0.075 rows=0 loops=1
                Buffers: shared hit=5
              ->  Bitmap Index Scan on products_locations_product_id_idx  (cost=0.00..103.53 rows=8051 width=0) (actual time=0.037..0.038 rows=0 loops=1)
                    Index Cond: (products_locations.product_id = en.product_id)
                    Buffers: shared hit=5
                    Worker 0:  actual time=0.037..0.038 rows=0 loops=1
                      Buffers: shared hit=5
Settings: work_mem = '20GB', effective_cache_size = '28GB', enable_seqscan = 'off', search_path = 'public, public, "$user"'
Planning Time: 1.424 ms
JIT:
  Functions: 24
  Options: Inlining true, Optimization true, Expressions true, Deforming true
  Timing: Generation 2.548 ms, Inlining 154.927 ms, Optimization 207.872 ms, Emission 113.548 ms, Total 478.894 ms
Execution Time: 15861.645 ms

If I pass directly the product_id that will be returned by the text search query in the query above I get a response back in a couple of ms:

explain(analyze, verbose, buffers, settings) 
select * from products_locations
where product_id in ('a14a064a-2dbf-4a05-9ccc-83c06f943a99');

Bitmap Heap Scan on public.products_locations  (cost=106.59..31314.44 rows=8002 width=356) (actual time=0.044..0.045 rows=0 loops=1)
  Output: id, product_id, store_id, aisle, latitude, longitude, username, creation_time, update_time, base_price, quantity, currency, fiscal_region, is_online, online_location, covered_regions, price_100, commit_id
  Recheck Cond: (products_locations.product_id = 'a14a064a-2dbf-4a05-9ccc-83c06f943a99'::uuid)
  Buffers: shared hit=4
  ->  Bitmap Index Scan on products_locations_product_id_idx  (cost=0.00..104.59 rows=8002 width=0) (actual time=0.039..0.040 rows=0 loops=1)
        Index Cond: (products_locations.product_id = 'a14a064a-2dbf-4a05-9ccc-83c06f943a99'::uuid)
        Buffers: shared hit=4
Settings: work_mem = '20GB', effective_cache_size = '28GB', enable_seqscan = 'off', search_path = 'public, public, "$user"'
Planning Time: 0.265 ms
Execution Time: 0.107 ms

If I run independently the pgroonga text search query, I also get a response fast:

explain(analyze, verbose, buffers, settings)
 SELECT product_id FROM search."en" WHERE 1=1 AND text &@ 'varza'

Index Scan using search_en_text_idx on search.en  (cost=0.00..4.01 rows=2277 width=16) (actual time=23.948..23.954 rows=1 loops=1)
  Output: product_id
  Index Cond: (en.text &@ 'varza'::text)
  Buffers: shared hit=1
Settings: work_mem = '20GB', effective_cache_size = '28GB', enable_seqscan = 'off', search_path = 'public, public, "$user"'
Planning Time: 2.242 ms
Execution Time: 26.064 ms

My search.en DB schema looks like this:

CREATE TABLE "search".en (
    id uuid NOT NULL,
    product_id uuid NOT NULL,
    "text" text NULL,
    username varchar(255) NOT NULL,
    creation_time timestamp NOT NULL,
    update_time timestamp NOT NULL,
    CONSTRAINT search_en_pkey PRIMARY KEY (id),
    CONSTRAINT search_en_product_id_locale_key UNIQUE (product_id),
    CONSTRAINT search_en_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products(id) ON DELETE CASCADE
);
CREATE INDEX search_en_char_based_text_idx ON search.en USING pgroonga (text) WITH (tokenizer='TokenMecab');
CREATE INDEX search_en_product_id_idx ON search.en USING btree (product_id);
CREATE INDEX search_en_text_idx ON search.en USING pgroonga (product_id, text);

My products_locations schema:

CREATE TABLE public.products_locations (
    id uuid NOT NULL,
    product_id uuid NOT NULL,
    store_id uuid NULL,
    aisle varchar(20) NULL,
    latitude numeric NULL,
    longitude numeric NULL,
    username varchar(255) NOT NULL,
    creation_time timestamp NOT NULL,
    update_time timestamp NOT NULL,
    base_price numeric NOT NULL,
    quantity int4 NOT NULL DEFAULT 0,
    currency varchar(50) NOT NULL,
    fiscal_region varchar(30) NULL DEFAULT NULL::character varying,
    is_online bool NULL DEFAULT false,
    online_location varchar(2048) NULL DEFAULT NULL::character varying,
    covered_regions text NULL,
    price_100 numeric NULL,
    commit_id uuid NULL,
    CONSTRAINT product_location_pkey PRIMARY KEY (id),
    CONSTRAINT products_locations_product_id_fkey FOREIGN KEY (product_id) REFERENCES public.products(id) ON DELETE CASCADE,
    CONSTRAINT products_locations_store_id_fkey FOREIGN KEY (store_id) REFERENCES public.stores(id) ON DELETE CASCADE
);
CREATE INDEX product_location_cr_eq ON public.products_locations USING btree (covered_regions);
CREATE INDEX product_location_cr_lk ON public.products_locations USING gin (covered_regions gin_trgm_ops);
CREATE INDEX product_location_ol_eq ON public.products_locations USING btree (online_location);
CREATE INDEX product_location_ol_lk ON public.products_locations USING gin (online_location gin_trgm_ops);
CREATE INDEX products_locations_id_idx ON public.products_locations USING btree (id);
CREATE INDEX products_locations_latitude_idx ON public.products_locations USING btree (latitude);
CREATE INDEX products_locations_longitude_idx ON public.products_locations USING btree (longitude);
CREATE INDEX products_locations_product_id_idx ON public.products_locations USING btree (product_id);
CREATE INDEX products_locations_store_id_idx ON public.products_locations USING btree (store_id);

Any idea how I can make postgres use the pgroonga indexes, when it should use them?

I’ve already tried to change:
random_page_cost = 1.0 / 0.5 / 5.0;
enable_seqscan = off/on;
run: ANALYZE

but without luck.

My postgres version is: PostgreSQL 16.1 (Debian 16.1-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit

DB/Pgroonga image container: groonga/pgroonga:3.1.7-debian-16

Thanks

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I managed to find a workaround by using WITH + MATERIALIZED in this way:

    explain(analyze, verbose, buffers, settings)
with v1 as MATERIALIZED (SELECT product_id FROM search."en" WHERE 1=1 AND text &@ 'varza')
select * from products_locations
where product_id in (select product_id from v1);

  2. 0

    The number of rows being overestimated by 300,000 fold is going to make the pgroonga index look less valuable than it is, and make the parallel query look more valuable than it is. Since a pgroonga index cannot be used for a Parallel Index Scan, it instead chooses a btree index which can be.

    There is probably not much you can do about the selectivity of &@ being so bad, but it is hard to see why it should think there are 8000 rows matching product_id = 'a14a064a-2dbf-4a05-9ccc-83c06f943a99', that should be investigated and fixed.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search