0

I am trying to speedup a fulltext search query. The testing table has 2 million rows, their values are md5 strings. I have created several indices to see, what performs best, but the planner chooses (seemingly) randomly with absolutely different results: from tens of milliseconds to tens of seconds.

The question is: how do I optimise my schema/indices/query so that I get stable and fast lookups (for this purpose I define fast as no more than a second, the less the better)


The database schema is like this: a table of packages, a table of items (which belong to a package) and a table attributes_list (attributes of an item). One package->many items, one item->many attributes.

CREATE TABLE items (
    id bigint NOT NULL GENERATED ALWAYS..., -- PK
    packageid bigint NULL, -- FK REFERENCES public.packages(id) 
    name text NOT NULL,
    CONSTRAINT items_name UNIQUE (name, package),
);

CREATE TABLE public.attributes_list (
    itemid bigint NOT NULL, -- FK REFERENCES public.items(id) 
    attributeid bigint NOT NULL, -- FK REFERENCES public."attributes"(id),
    value text NOT NULL,
);

CREATE INDEX items_attributes_list_attributeid_index ON public.items_attributes_list
    USING btree (attributeid);
CREATE INDEX items_attributes_list_itemid_index ON public.items_attributes_list
    USING btree (itemid);

-- a bunch of indices to see what performs best:
CREATE INDEX items_attributes_list_trgm_GIN_idx ON items_attributes_list USING GIN (value gin_trgm_ops);
CREATE INDEX items_attributes_list_trgm_idx ON items_attributes_list USING GIST (value gist_trgm_ops);
CREATE INDEX items_attributes_list_text_ptrn_ops ON public.items_attributes_list  USING btree (value text_pattern_ops);
CREATE INDEX items_attributes_list_value_text_search ON public.items_attributes_list USING btree (itemid, value text_pattern_ops);

The query I execute finds attributes value by prefix:

SELECT value FROM attributes_list
JOIN items ON attributes_list.itemid=items.id
WHERE (
    attributes_list.attributeid = $1 AND
    items.packageid = $2 AND
    attributes_list.value [I]LIKE $3 -- ILIKE is preferred. Guaranteed not to start with a wildcard
)
ORDER BY itemid LIMIT 6

Then I start querying with randomly generated prefixes and I see different plans with different indices involved and with drastically different execution time:

-- ILIKE 'a4071%'
Limit  (cost=699.50..699.51 rows=6 width=41) (actual time=13435.488..13435.494 rows=5 loops=1)
  Output: items_attributes_list.value, items_attributes_list.itemid
  ->  Sort  (cost=699.50..700.00 rows=200 width=41) (actual time=13435.486..13435.490 rows=5 loops=1)
        Output: items_attributes_list.value, items_attributes_list.itemid
        Sort Key: items_attributes_list.itemid
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=0.84..695.91 rows=200 width=41) (actual time=69.799..13435.425 rows=5 loops=1)
              Output: items_attributes_list.value, items_attributes_list.itemid
              Inner Unique: true
              ->  Index Scan using items_attributes_list_trgm_idx on public.items_attributes_list  (cost=0.41..209.41 rows=200 width=41) (actual time=67.419..13421.649 rows=5 loops=1)
                    Output: items_attributes_list.itemid, items_attributes_list.attributeid, items_attributes_list.value
                    Index Cond: (items_attributes_list.value ~~* 'a4071%'::text)
                    Filter: (items_attributes_list.attributeid = '11'::bigint)
              ->  Index Scan using packages_items_pkey on public.items  (cost=0.43..2.43 rows=1 width=8) (actual time=2.747..2.747 rows=1 loops=5)
                    Output: items.id, items.packageid, items.name, items.created, 
                    Index Cond: (items.id = items_attributes_list.itemid)
                    Filter: (items.packageid = '2'::bigint)
Settings: effective_cache_size = '2341MB', effective_io_concurrency = '18', maintenance_io_concurrency = '18', random_page_cost = '1', work_mem = '4021kB'
Planning Time: 49.671 ms
Execution Time: 13435.679 ms

-- ILIKE '64c03%'
Limit  (cost=492.66..492.68 rows=6 width=41) (actual time=5606.157..5606.161 rows=2 loops=1)
  Output: items_attributes_list.value, items_attributes_list.itemid
  ->  Sort  (cost=492.66..493.16 rows=200 width=41) (actual time=5606.155..5606.158 rows=2 loops=1)
        Output: items_attributes_list.value, items_attributes_list.itemid
        Sort Key: items_attributes_list.itemid
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=0.98..489.08 rows=200 width=41) (actual time=4273.646..5606.085 rows=2 loops=1)
              Output: items_attributes_list.value, items_attributes_list.itemid
              Inner Unique: true
              ->  Index Scan using items_attributes_list_text_ptrn_ops on public.items_attributes_list  (cost=0.55..2.58 rows=200 width=41) (actual time=4270.954..5599.248 rows=2 loops=1)
                    Output: items_attributes_list.itemid, items_attributes_list.attributeid, items_attributes_list.value
                    Index Cond: ((items_attributes_list.value ~>=~ '64'::text) AND (items_attributes_list.value ~<~ '65'::text))
                    Filter: ((items_attributes_list.value ~~* '64c03%'::text) AND (items_attributes_list.attributeid = '11'::bigint))
                    Rows Removed by Filter: 7908
              ->  Index Scan using packages_items_pkey on public.items  (cost=0.43..2.43 rows=1 width=8) (actual time=3.405..3.406 rows=1 loops=2)
                    Output: items.id, items.packageid, items.name, items.created, 
                    Index Cond: (items.id = items_attributes_list.itemid)
                    Filter: (items.packageid = '2'::bigint)
Settings: effective_cache_size = '2341MB', effective_io_concurrency = '18', maintenance_io_concurrency = '18', random_page_cost = '1', work_mem = '4021kB'
Planning Time: 6.519 ms
Execution Time: 5608.636 ms

-- ILIKE '11787%'
Limit  (cost=492.66..492.68 rows=6 width=41) (actual time=11.730..11.733 rows=4 loops=1)
  Output: items_attributes_list.value, items_attributes_list.itemid
  ->  Sort  (cost=492.66..493.16 rows=200 width=41) (actual time=11.728..11.730 rows=4 loops=1)
        Output: items_attributes_list.value, items_attributes_list.itemid
        Sort Key: items_attributes_list.itemid
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=0.98..489.08 rows=200 width=41) (actual time=5.549..11.675 rows=4 loops=1)
              Output: items_attributes_list.value, items_attributes_list.itemid
              Inner Unique: true
              ->  Index Scan using items_attributes_list_text_ptrn_ops on public.items_attributes_list  (cost=0.55..2.58 rows=200 width=41) (actual time=2.658..3.974 rows=4 loops=1)
                    Output: items_attributes_list.itemid, items_attributes_list.attributeid, items_attributes_list.value
                    Index Cond: ((items_attributes_list.value ~>=~ '11787'::text) AND (items_attributes_list.value ~<~ '11788'::text))
                    Filter: ((items_attributes_list.value ~~* '11787%'::text) AND (items_attributes_list.attributeid = '11'::bigint))
              ->  Index Scan using packages_items_pkey on public.items  (cost=0.43..2.43 rows=1 width=8) (actual time=1.921..1.921 rows=1 loops=4)
                    Output: items.id, items.packageid, items.name, items.created, 
                    Index Cond: (items.id = items_attributes_list.itemid)
                    Filter: (items.packageid = '2'::bigint)
Settings: effective_cache_size = '2341MB', effective_io_concurrency = '18', maintenance_io_concurrency = '18', random_page_cost = '1', work_mem = '4021kB'
Planning Time: 103.631 ms
Execution Time: 11.959 ms

I also tried to execute case-sensitive searches, but with no visible success too:

-- LIKE '3b%'
Limit  (cost=492.66..492.68 rows=6 width=41) (actual time=4815.444..4815.447 rows=6 loops=1)
  Output: items_attributes_list.value, items_attributes_list.itemid
  ->  Sort  (cost=492.66..493.16 rows=200 width=41) (actual time=4815.443..4815.444 rows=6 loops=1)
        Output: items_attributes_list.value, items_attributes_list.itemid
        Sort Key: items_attributes_list.itemid
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Nested Loop  (cost=0.98..489.08 rows=200 width=41) (actual time=1.669..4805.308 rows=7706 loops=1)
              Output: items_attributes_list.value, items_attributes_list.itemid
              Inner Unique: true
              ->  Index Scan using items_attributes_list_text_ptrn_ops on public.items_attributes_list  (cost=0.55..2.58 rows=200 width=41) (actual time=1.611..2126.767 rows=7706 loops=1)
                    Output: items_attributes_list.itemid, items_attributes_list.attributeid, items_attributes_list.value
                    Index Cond: ((items_attributes_list.value ~>=~ '3b'::text) AND (items_attributes_list.value ~<~ '3c'::text))
                    Filter: ((items_attributes_list.value ~~ '3b%'::text) AND (items_attributes_list.attributeid = '11'::bigint))
              ->  Index Scan using packages_items_pkey on public.items  (cost=0.43..2.43 rows=1 width=8) (actual time=0.345..0.345 rows=1 loops=7706)
                    Output: items.id, items.packageid, items.name
                    Index Cond: (items.id = items_attributes_list.itemid)
                    Filter: (items.packageid = '2'::bigint)
Settings: effective_cache_size = '2341MB', effective_io_concurrency = '18', maintenance_io_concurrency = '18', random_page_cost = '1', work_mem = '4021kB'
Planning Time: 3.025 ms
Execution Time: 4815.580 ms


-- LIKE '2d%'
Limit  (cost=0.85..22.12 rows=6 width=41) (actual time=4.192..7.994 rows=6 loops=1)
  Output: items_attributes_list.value, items_attributes_list.itemid
  ->  Nested Loop  (cost=0.85..71585.48 rows=20202 width=41) (actual time=4.191..7.988 rows=6 loops=1)
        Output: items_attributes_list.value, items_attributes_list.itemid
        Inner Unique: true
        ->  Index Scan using items_attributes_list_value_text_search on public.items_attributes_list  (cost=0.43..42720.08 rows=20202 width=41) (actual time=4.153..5.705 rows=6 loops=1)
              Output: items_attributes_list.itemid, items_attributes_list.attributeid, items_attributes_list.value
              Index Cond: ((items_attributes_list.value ~>=~ '2d'::text) AND (items_attributes_list.value ~<~ '2e'::text))
              Filter: ((items_attributes_list.value ~~ '2d%'::text) AND (items_attributes_list.attributeid = '11'::bigint))
        ->  Index Scan using packages_items_pkey on public.items  (cost=0.43..1.43 rows=1 width=8) (actual time=0.377..0.377 rows=1 loops=6)
              Output: items.id, items.packageid, items.name
              Index Cond: (items.id = items_attributes_list.itemid)
              Filter: (items.packageid = '2'::bigint)
Settings: effective_cache_size = '2341MB', effective_io_concurrency = '18', maintenance_io_concurrency = '18', random_page_cost = '1', work_mem = '4021kB'
Planning Time: 29.733 ms
Execution Time: 8.160 ms

PostgreSQL 13.12 on x86_64-pc-linux-gnu, compiled by gcc, a b1ae812d88 p 97651dedcb, 64-bit If it's important, I query with golang 1.21, pgx/v4.

2 Answers 2

2

Signature-based GiST indexes (such as trigram) generally don't perform well when they are large, and the planner has trouble estimating them accurately and so will use them even when they are not appropriate. The GiST index is "masking" the GIN index, which is probably more suitable to this task.

text_pattern_ops does not get along well with ILIKE. '64c03%' for example has to get truncated before the first Latin letter (which of course has different case possibilities, unlike digits), leading the index usage being much less selective than it otherwise could be. You say that 'ILIKE is preferred' but don't say why. That preference comes at a high cost.

One index that might be highly useful is (attributeid, value text_pattern_ops), particularly if you were to use LIKE rather than ILIKE in the query. Then the selectivity of the attributeid test and the pattern could be compounded rather than needing to use just one and filter on the other.

The 2nd to the last query is different than all the others in that is finds a large number of rows that need to be processed before the LIMIT can be applied. This one might benefit from a different index, (attributeid, itemid, value text_pattern_ops). That way the ORDER BY can be fulfilled directly from the index, letting the query finish early once it finds 6 rows without needing to read all the rest of the rows.

1
  • Thank you for your reply. I'm going to evaluate all the options, but about the last index you suggested: does the order of the first two columns matter? Oct 6, 2023 at 14:58
1

If the search pattern doesn't start with no wildcard and is no parameter ($1), then a B-tree index with the text_pattern_ops operator class is best. If you want case insensitive search, rewrite the condition to

WHERE lower(attributes_list.value) LIKE lower('some%pattern')

and create the index on lower(value).

It is normal that the execution time is proportional to the number of rows found.

You can speed up the index scan on items if you create an index that contains all required columns:

CREATE INDEX ON items (id, packageid);
-- to get a fast index-only scan
VACUUM items;

Since you mention that you do have parameters in your query, a way out is to set plan_cache_mode = force_custom_plan. Then PostgreSQL will always substitute the parameters like it did in the execution plans you show, and you can benefit from the index recommended in my answer.

1
  • Thanks. Unfortunately, all three parameters are mandatory and I can't see a way to get rid of them. Oct 6, 2023 at 14:59

Your Answer

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

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