Re: CREATE INDEX ON words_moves(length(letters), score) WHERE action= 'play'

On 2019-12-07 16:20:44 +0100, Alexander Farber wrote:
> in PostgreSQL 11 I have a table holding player moves (could be: 'play', 'swap',
> 'skip', ...) in a word game:
>
> # \d words_moves;
>                                       Table "public.words_moves"
>  Column  |           Type           | Collation | Nullable |                
> Default                  
> ---------+--------------------------+-----------+----------+------------------------------------------
>  mid     | bigint                   |           | not null | nextval
> ('words_moves_mid_seq'::regclass)
>  action  | text                     |           | not null |
>  gid     | integer                  |           | not null |
>  uid     | integer                  |           | not null |
>  played  | timestamp with time zone |           | not null |
>  tiles   | jsonb                    |           |          |
>  score   | integer                  |           |          |
>  letters | text                     |           |          |
>  hand    | text                     |           |          |
>  puzzle  | boolean                  |           | not null | false
[...]
> When I search for "interesting moves" with score higher than 90 or all 7 tiles
> played, then the query takes a bit longer:
>
> EXPLAIN ANALYZE 
> SELECT 
[...]
> FROM words_moves 
>  WHERE action = 'play'  
> AND LENGTH(hand) = 7 
> AND (LENGTH(letters) = 7 OR score > 90) 
> AND played > CURRENT_TIMESTAMP - interval '1 year' 
> AND played < CURRENT_TIMESTAMP - interval '3 day'  
>  ORDER BY played DESC;
>                                                                                
[...]
> Then I drop that index and create another one:
>
> CREATE INDEX ON words_moves(length(letters), score) WHERE action = 'play';
>
> And it seems to have a better performance on the query:
>
>                                                                              
> QUERY PLAN                                                                    
>            
>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Sort  (cost=97687.61..97687.72 rows=44 width=97) (actual time=175.832..175.927
> rows=1036 loops=1)
>    Sort Key: played DESC
>    Sort Method: quicksort  Memory: 194kB
>    ->  WindowAgg  (cost=97684.98..97686.41 rows=44 width=97) (actual time= > 172.443..174.937 rows=1036 loops=1)
>          ->  Sort  (cost=97684.98..97685.09 rows=44 width=57) (actual time= > 172.390..172.490 rows=1036 loops=1)
>                Sort Key: (to_char(played, 'Mon YYYY'::text))
>                Sort Method: quicksort  Memory: 129kB
>                ->  Bitmap Heap Scan on words_moves  (cost=55092.96..97683.78 > rows=44 width=57) (actual
time=165.420..171.164rows=1036 loops=1) 
>                      Recheck Cond: (((length(letters) = 7) AND (action = > 'play'::text)) OR ((score > 90) AND
(action= 'play'::text))) 
>                      Filter: ((length(hand) = 7) AND (played > > (CURRENT_TIMESTAMP - '1 year'::interval)) AND
(played< (CURRENT_TIMESTAMP - '3 > days'::interval))) 
>                      Rows Removed by Filter: 468
>                      Heap Blocks: exact=1495
>                      ->  BitmapOr  (cost=55092.96..55092.96 rows=15280 width=0) > (actual time=165.036..165.036
rows=0loops=1) 
>                            ->  Bitmap Index Scan on > words_moves_length_score_idx  (cost=0.00..275.71 rows=14838
width=0)(actual > time=0.620..0.620 rows=912 loops=1) 
>                                  Index Cond: (length(letters) = 7)
>                            ->  Bitmap Index Scan on > words_moves_length_score_idx  (cost=0.00..54817.23 rows=442
width=0)(actual > time=164.413..164.413 rows=608 loops=1) 
>                                  Index Cond: (score > 90)
>  Planning Time: 0.948 ms
>  Execution Time: 177.604 ms
> (19 rows)
>
> Here the resulting link: https://explain.depesz.com/s/pmCw
>
> I still wonder, what am I missing, what could be improved there?

Several ideas:

 1. You are only interested in moves from the last year or so. How much
    history do have stored in your table? If it's much more than one
    year, then an index on played will help. However, it looks like that
    filter removes at most 468 of ~ 1500 rows, so that doesn't seem to
    be case (yet).

 2. You spend now most of the time scanning words_moves_length_score_idx
    for scores > 90. This is basically a full index scan since that
    index is ordered by length(letters). I'm surprised that PostgreSQL
    can even do that :-). This is a separate index scan than the one for
    length(letters) = 7, so separating the indexes should be no worse
    and probably a lot better. Create an index on score (possibly
    conditional on action = 'play').

 3. A lot of the conditions is fixed. So you might want to move them into
    the condition of a partial index:

        create index on words_moves(played)
            where action = 'play' and LENGTH(hand) = 7 and (LENGTH(letters) = 7 OR score > 90);

    Then the planner should recognize that it can use this index and the
    index contains only interesting moves - no logical operations needed
    at query time at all, only an index scan to find recent moves.

    Be warned though that creating such ultra-specific indexes comes at
    a cost: You may end up with a lot of them if you have many different
    queries and maintaining them may make inserts noticably slower.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"