Question 90267 in PostgreSQL Question posted in
The official documentation can be found here.

How to improve or speed up Postgres query with pg_trgm? – Postgresql

Are there any additional steps I can take to speed up query execution?

I have a table with more than 100m rows and I need to do search for matching strings. For that I checked two options:

  1. Compare text with to_tsvector @@ (to_tsquery or plainto_tsquery)
    This works very fast (under 1s on all data) but it has some problems with finding text similarity
  2. Compare text with pg_trgm similarity
    This works fine on text comparison but works bad on large amount of data.

I found that I can use indexes to improve performance.
For my GiST index I tried to increase siglen from small number to 2024, but for some reason Postgres uses 512 and not higher.

CREATE INDEX trgm_idx_512_gg ON table USING GIST (name gist_trgm_ops(siglen=512));

Query:

SELECT name, similarity(name, 'ноутбук MSI GF63 Thin 10SC 086XKR 9S7 16R512 086') as sm
FROM table
WHERE name % 'ноутбук MSI GF63 Thin 10SC 086XKR 9S7 16R512 086'

EXPLAIN output:

Bitmap Heap Scan on table (cost=1632.01..40051.57 rows=9737 width=126)
  Recheck Cond: ((name)::text % 'ноутбук MSI GF63 Thin 10SC 086XKR 9S7 16R512 086'::text)
  ->  Bitmap Index Scan on trgm_idx_512_gg  (cost=0.00..1629.57 rows=9737 width=0)
        Index Cond: ((name)::text % 'ноутбук MSI GF63 Thin 10SC 086XKR 9S7 16R512 086'::text)

Execution time was about 120 sec.

Question

How can I improve or speed up query? Maybe I need to use a different approach or just add something else?

Output for EXPLAIN (ANALYZE, BUFFERS) (searching for a different name so that the search is completely new and not from the cache):

Bitmap Heap Scan on table (cost=1632.01..40051.57 rows=9737 width=126) (actual time=159119.258..159960.251 rows=5645 loops=1)
  Recheck Cond: ((name)::text % 'Чехол на realme C25s / Реалми Ц25с c рисунком / прозрачный с принтом, Andy&Paul'::text)
  Heap Blocks: exact=3795
  Buffers: shared read=1289378
  ->  Bitmap Index Scan on trgm_idx_512_gg  (cost=0.00..1629.57 rows=9737 width=0) (actual time=159118.616..159118.616 rows=5645 loops=1)
        Index Cond: ((name)::text % 'Чехол на realme C25s / Реалми Ц25с c рисунком / прозрачный с принтом, Andy&Paul'::text)
        Buffers: shared read=1285583
Planning:
  Buffers: shared read=5
Planning Time: 4.063 ms
Execution Time: 159961.121 ms

I also created a GIN index (but Postgres kept using the GiST):

CREATE INDEX gin_gg ON table USING GIN (name gin_trgm_ops);

Size: 12 GB.

GIST index: 31GB

Tags:

2

Answers


  1. 0

    Your indexes are correct, besides that, here are some strategies in order of importance according to this specific case to tune your query performance:

    • In your execution plan can be noticed that buffers are being read
      instead of hit       (means that data is missing from prostgres buffer
      cache and is read from disk) see Using buffers for query
      optimization      .

      Issue a SELECT setting, unit FROM pg_settings WHERE name = 'shared_buffers'; to see the size of your database buffers (that
      number must be mutiplied by unit, 80k usually to see actual size in
      kbs).

      It’s recommended 25% of server’s RAM for buffers, and usually
      is left on it’s defult value, that is 16384 * 8kb = 128Mb, but
      should be adapt to the situation. In tests i have seen a slight
      improvement (not much when storage is in SSDs) in query performance, you can change this parameter in postgresql.conf file and a database restart is
      required.

    • Peform a vacuum verbose analyze <table>; to check if there is data
      blocked by pending transactions (dead rows blocked) and at the same
      time execute a vacuum analyze operation, the PostgreSQL query planner
      relies on statistical information about the contents of tables in
      order to generate good plans for queries. These statistics are
      gathered by the ANALYZE command, which can be invoked by itself or as
      an optional step in VACUUM. It is important to have reasonably
      accurate statistics, otherwise poor choices of plans might degrade
      database performance, see
      vaccuming.

      For many installations, it is sufficient to let vacuuming be
      performed by the autovacuum daemon, but some database administrators
      will want to supplement or replace the daemon’s activities with
      manually-managed VACUUM commands, which typically are executed
      according to a schedule by cron or Task Scheduler scripts. This
      updates the visibility map, which speeds up index-only scans (which
      in your case doesn’t fully apply, because mainly, data is obtained by
      heap scan).

    • Could execute a cluster <table> using <index>; (when database is
      out of production because its a blocking method) to reorganize data
      for faster access, however for this use case i don’t see performance
      improvement, see sql-cluster.

    Login or Signup to reply.
  2. 0

    A trigram GiST index with siglen=512 on 100m rows is very large, and will probably never be cached efficiently. (Default is siglen=12 i.e. 12 bytes.) What makes you think this large signature would be a good choice? The manual:

    Longer signatures lead to a more precise search (scanning a smaller fraction of the index and fewer heap pages), at the cost of a larger index.

    Looks like you went overboard with the size.

    I have better experience with a trigram GIN indexes, especially in current versions of Postgres. If the query planner is confused by the existence of an additional GiST index, you’ll have to remove that one, to test results with the GIN index.

    But first, to get a size comparison, look at the output of:

    SELECT i.indexrelid::regclass::text AS idx
     , pg_get_indexdef(i.indexrelid) AS idx_def
     , pg_size_pretty(pg_relation_size(i.indexrelid)) AS idx_size
FROM   pg_class t
JOIN   pg_index i ON i.indrelid = t.oid
WHERE  t.oid = 'public.tbl'::regclass  -- your table name here!
ORDER  BY 1;

    (Ideally, add the result to the questions.)

    Your query plan shows vast amounts of Buffers: shared read for index and main relation (heap). So nothing was found in cache. The key to better performance will be to read fewer data pages to satisfy your queries, and more of them from cache: hit instead of read in the query plan.

    Reducing the size of table and indexes helps in this reagard.

    The selectivity of the trigram similarity operator % is set by the customized option pg_trgm.similarity_threshold. The default 0.3 is rather lax and allows many hits. A higher similarity threshold will filter fewer (better matching) result rows. What do you do with rows=5645 result rows anyway? Try:

    SET pg_trgm.similarity_threshold = 0.5;  -- or higher

    Then retry your query.
    See:

    The latest version or Postgres, better server configuration and more RAM can also help in this regard. You disclosed no information about either of these.

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

Back To Top
Search