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

[Peter J. Holzer]

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 '::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 pa

canno save editted data

[Patrick Fiset]

Hi,

When I do "view/edit data", and change a cell, I cannot click the save
button (it's greyed out).

Why is it greyed out ?

If I close the "view/edit data", it tells that it's not saved, and now I
can click "save" on the popup.

Re: canno save editted data

[Adrian Klaver]

On 12/8/19 11:54 AM, Patrick Fiset wrote:

Hi,

When I do "view/edit data", and change a cell, I cannot click the save 
button (it's greyed out).


Why is it greyed out ?

If I close the "view/edit data", it tells that it's not saved, and now I 
can click "save" on the popup.





You are going to need to provide more information:

1) What application(and version) are you using above?

2) Postgres version?

3) OS and version?


--
Adrian Klaver
adrian.kla...@aklaver.com

Re: canno save editted data

[Adrian Klaver]

On 12/8/19 12:25 PM, Patrick Fiset wrote:

Please reply to list also.
Ccing list.



It is pgAdmin4, version 4.15, running on Windows 10.


I see two Save buttons. The leftmost one(floppy disk icon) is for saving 
a query. This does not seem to be enabled when you are using the query 
created by View/Edit Data. The Save you are looking for is next to 
it(Grid with down arrow). Also available using F6. It becomes active 
when you make a change in the data grid.




On Sun, 8 Dec 2019 at 15:09, Adrian Klaver > wrote:


On 12/8/19 11:54 AM, Patrick Fiset wrote:
 > Hi,
 >
 > When I do "view/edit data", and change a cell, I cannot click the
save
 > button (it's greyed out).
 >
 > Why is it greyed out ?
 >
 > If I close the "view/edit data", it tells that it's not saved,
and now I
 > can click "save" on the popup.
 >


You are going to need to provide more information:

1) What application(and version) are you using above?

2) Postgres version?

3) OS and version?


-- 
Adrian Klaver

adrian.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com