Slow query because lexeme index not used

From: Alex <cdalxndr(at)yahoo(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Slow query because lexeme index not used
Date: 2021-08-07 19:35:25
Message-ID: 1557921338.669347.1628364925994@mail.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Table "product" has a GIN index on "lexeme" column (tsvector) that is not used.

Query that doesn't use lexeme idx:  https://explain.dalibo.com/plan/BlB#plan, ~8s, ~60.000 blocks needed

Query forced to use lexeme idx: https://explain.dalibo.com/plan/i52, ~800ms (10x less), ~15.000 blocks needed (x4 less)
Table metdata:
         relname          | relpages | reltuples | relallvisible | relkind | relnatts | relhassubclass | reloptions | pg_table_size
--------------------------+----------+-----------+---------------+---------+----------+----------------+------------+---------------
 product_property_default |     8992 |    622969 |          8992 | r       |       16 | f              |            |      73719808
 product                  |    49686 |    413840 |         49686 | r       |       14 | f              |            |     493314048
Table stats:
   frac_mcv    |        tablename         | attname | inherited | null_frac | n_distinct  | n_mcv | n_hist | correlation
---------------+--------------------------+---------+-----------+-----------+-------------+-------+--------+-------------
               | product                  | lexeme  | f         |         0 |          -1 |       |        |
    0.99773335 | product_property_default | meaning | f         |         0 |          63 |    39 |     24 |  0.19444875
     0.6416333 | product_property_default | first   | f         |         0 |        2193 |   100 |    101 | -0.09763639
 0.00023333334 | product_property_default | product | f         |         0 | -0.15221785 |     1 |    101 |  0.08643274

Using windows docker with wsl2.Both cases are run with cold cache.All database memory is limited to 1GB by using .wslconfig file with memory=1GB, also the docker container is limited to 1GB.
My requirement is to optimize disk access with this limited memory

Postgres 12.4

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Justin Pryzby 2021-08-08 00:35:28 Re: Slow query because lexeme index not used
Previous Message Jeff Janes 2021-08-05 20:03:00 Re: Logical Replication speed-up initial data