0

After running the same query twice I expected data to be cached, but I see data is still read from disk:

->  Index Scan using product_pkey on product  (cost=0.42..3.51 rows=1 width=4) (actual time=0.132..0.132 rows=0 loops=25713)
                           Index Cond: (id = product_property_default.product)
                           Filter: ((lexeme @@ '''laptop'' | ''laptop-ul'''::tsquery) AND ((language)::oid = '20657'::oid))
                           Rows Removed by Filter: 1
                           Buffers: shared hit=152936 read=6111
                           I/O Timings: read=2602.604

Full plan: https://explain.dalibo.com/plan/oJUn

Query:

explain (analyze, buffers)
select distinct "product"."id"
from "product"
    inner join product_property on product_property.product = product.id
where "product"."lexeme" @@ plainto_tsquery('ro'::regconfig, 'laptop')
    and "product_property"."meaning" = 'B'
    and "product_property"."first" in (1.7179869184E10)
    and "product"."language" = 'ro'::regconfig;

My index size is 38MB:

\di+ product_pkey
                            List of relations
 Schema |     Name     | Type  |  Owner   |  Table  | Size  | Description
--------+--------------+-------+----------+---------+-------+-------------
 pse    | product_pkey | index | postgres | product | 38 MB |

Why total buffers count (x8KB) is larger than my index that is only 38MB?

Does it include database fetched rows?

Table size 755MB:

\dt+ product
                     List of relations
 Schema |  Name   | Type  |  Owner   |  Size  | Description
--------+---------+-------+----------+--------+-------------
 pse    | product | table | postgres | 755 MB |

Config:

  • work_mem=64MB
  • effective_cache_size=512MB
  • shared_buffers=256MB

My database docker container uses low memory. From docker stats:

NAME                CPU %               MEM USAGE / LIMIT   MEM %               NET I/O             BLOCK I/O
db                  0.32%               75.36MiB / 512MiB   14.72%              359MB / 406MB       64.5GB / 14GB

Note that there are other docker services running on the same machine.

free -h:

              total        used        free      shared  buff/cache   available
Mem:           3.9G        2.9G        143M        274M        850M        488M
Swap:          4.0G        1.7G        2.3G

Using Postgres 12.4 (docker image postgres:12.4-alpine)

4
  • Table and index DDL would be helpful. Aug 4, 2021 at 10:31
  • 1
    You do realise that Postgres uses the OS filesystem cache extensively? Aug 4, 2021 at 11:30
  • @Colin'tHart I see 850M cached but my index is only 38MB. Also the total buffer count seems high. Do the buffers read number include rows read from actual table?
    – cdalxndr
    Aug 4, 2021 at 11:54
  • 2
    Though this particual index may be relatively small, your query has to read seven other indexes and a table, which all compete for your puny 256 MB of shared buffers.
    – mustaccio
    Aug 4, 2021 at 12:06

1 Answer 1

2

Your index scan is not an index-only scan. It has to hit the table for every row it finds in the index. And it looks like "lexeme" is large and has been TOASTed, so it has to read multiple multiple pages to reassemble the full value. All this disk access is shown in one node, so it is not obvious how much is for the index, and how much is for the table driven from the index.

Do you have a FTS index on lexeme, and it is just not getting used? Or is the index missing?

8
  • I have a GIN index on lexeme, size 151M. Also my lexeme column has weights.
    – cdalxndr
    Aug 4, 2021 at 15:15
  • It might be faster to use the lexeme index than to do what it is currently doing, but that is not certain. If you want to dig into that, you should probably post a new question focusing on that topic. You can probably get rid of the joins on offer, stock, and stock_template, as none of them seem to be relevant to the performance issue and just complicate things, but please show the text of the rest of the query.
    – jjanes
    Aug 4, 2021 at 20:42
  • updated question with simplified query & plan
    – cdalxndr
    Aug 4, 2021 at 22:39
  • Did a simple test and the lexeme index is used when I do a simple query with @@ without any joins. Don't know why the planner decides not to use it in my original query.
    – cdalxndr
    Aug 4, 2021 at 22:42
  • @cdalxndr How fast was it? How many rows did it expect to find? How many did it actually find? Presumably the planner didn't use it because it thought it would be slower to do so.
    – jjanes
    Aug 5, 2021 at 1:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.