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.164 rows=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=0 loops=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@xxxxxx | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachment:
signature.asc
Description: PGP signature
