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
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 |