PostgreSQL Trigram索引与btree

Question

我为我的cake数据库提供了一个搜索查询，该查询目前非常缓慢，我希望对它进行改进。我负责PostgreSQL诉9.6。

表结构：

Table: Cakes
=====
id               int
cake_id          varchar
cake_short_name  varchar
cake_full_name   varchar
has_recipe       boolean
createdAt        Datetime
updatedAt        DateTime


Table: CakeViews
=========
id               int
cake_id          varchar
createdAt        Datetime
updatedAt        DateTime

查询：

    WITH myconstants (myquery, queryLiteral) as (
       values ('%a%', 'a')
    )
    
        select
        full_count,
        cake_id,
        cake_short_name,
        cake_full_name,
        has_recipe,
        views
        from (
          select
            count(*) OVER() AS full_count,
            cake_id,
            cake_short_name,
            cake_full_name,
            has_recipe
            cast((select count(*) FROM "CakeViews" as cv where "createdAt" > CURRENT_DATE - 3 and c.cake_id = cv.cake_id) as integer) as views
          from "Cakes" c, myconstants
          where has_recipe = true 
            and (cake_full_name ilike myquery or cake_short_name ilike myquery)
            or cake_full_name ilike lower(queryLiteral) or cake_short_name ilike lower(queryLiteral)) t, myconstants
        order by views desc,
          case
            when cake_short_name ilike lower(queryLiteral) then 1
            when cake_full_name ilike lower(queryLiteral) then 1
          end,
          case
            when has_recipe = true and cake_short_name ilike myquery then length(cake_short_name)
            when has_recipe = true and cake_full_name ilike myquery then length(cake_full_name)
          end
            limit 10

我对以下索引有一些想法，但它们并没有大大加快查询的速度：

    CREATE EXTENSION pg_trgm;
    CREATE INDEX idx_cakes_cake_short_name ON public."Cakes" (lower(cake_short_name) varchar_pattern_ops);
    CREATE INDEX idx_cakes_cake_id ON public."Cakes" (cake_short_name);
    
    CREATE INDEX idx_cakeviews_cake_id ON public."CakeViews" (cake_id);
    CREATE INDEX idx_cakes_cake_short_name ON public."Cakes" USING gin (cake_short_name gin_trgm_ops);
    CREATE INDEX idx_cakes_cake_full_name ON public."Cakes" USING gin (cake_full_name gin_trgm_ops);

问题

• 哪些指数会更好，或者我错过了哪些指标？
• 我的查询效率低吗？

编辑：解释分析输出：这里

Answer 1

查询'%a%‘不包含任何trigrams，因此索引在那里不会有用。它必须扫描整张桌子。但是如果您使用了更长的查询，那么它们可能是有用的。

如果是on "CakeViews" (cake_id)，指数on "CakeViews" (cake_id, "createdAt")会更好。除了你的蛋糕似乎没有任何意见，所以如果是这样的话，我想这并不重要。