pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Alex <cdalxndr@yahoo.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Slow query because lexeme index not used
Date: Sat, 7 Aug 2021 19:35:25 +0000 (UTC)
Message-ID: <1557921338.669347.1628364925994@mail.yahoo.com> (raw)
References: <1557921338.669347.1628364925994.ref@mail.yahoo.com>

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

view thread (3+ messages)  latest in thread

Message-ID: <1557921338.669347.1628364925994@mail.yahoo.com>
Permalink:  ../1557921338.669347.1628364925994@mail.yahoo.com/
Also on:    postgresql.org/message-id/1557921338.669347.1628364925994@mail.yahoo.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: cdalxndr@yahoo.com
  Subject: Re: Slow query because lexeme index not used
  In-Reply-To: <1557921338.669347.1628364925994@mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox