Slow query because lexeme index not used

Поиск
Список
Период
Сортировка
От Alex
Тема Slow query because lexeme index not used
Дата
Msg-id 1557921338.669347.1628364925994@mail.yahoo.com
обсуждение исходный текст
Ответы Re: Slow query because lexeme index not used  (Justin Pryzby <pryzby@telsasoft.com>)
Список 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








В списке pgsql-performance по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: Logical Replication speed-up initial data
Следующее
От: Justin Pryzby
Дата:
Сообщение: Re: Slow query because lexeme index not used