Yorhel's VNDB Development ramblings

Posted in

#26 by skorpiondeath
2019-10-15 at 17:10
Ok I'm more in SQLServer than Postgre but usually there are 3 things that I know that you can do to enhance DB performance:
1) keep statistics updated, you should have a AUTOVACUUM thread deamon in postgre that automatically do VACUUM (garbage collector) + ANALYZE (update statistics)
2) Every now and then rebuild fragmented index that should be REINDEX command
3) I keep on referring about clustered index because that's what happens automatically if you do a PRIMARY KEY on SQLServer, reading a bit I just found that's not the same for postgresql link.
They have their own CLUSTER command link to launch on a primary key or index. I don't know if vndb has those. But I guess you should try if you don't because that can help speed up query execution. Cluster should definetly speed up counts and accessing range of indexed values from a table (pagination)Last modified on 2019-10-15 at 17:15
#27 by skorpiondeath
2019-10-15 at 21:51
Ok Yorhel, just studied a bit more and sadly nope clustered index does not exist in Postgre :( link
I was assuming CLUSTER did that but it seems to just reorder the data upon the index you choose. The query just keep on doing a "Sequential Scan" for a count over lot of rows. In SQL Server instead a "table scan" become a "clustered index scan" which is faster.

I finally checked the execution plan thanks to your command and index worked fine. Probably you could try to CLUSTER a table anyway on a backup since it could be a good idea to see what happens and it could prove beneficial (but not the way I expected to be).Last modified on 2019-10-15 at 21:58
#28 by yorhel
2019-10-18 at 09:25
I'll play around with those performance things when I get back to fixing proper pagination for history listings.

Currently brainstorming about improving the lists feature. I think this covers the intended semantics quite well: link

Only downside so far is that with those changes in place, all votes that aren't also tagged with a label will be private by default. One possible way to improve that might be to automatically assign the 'Finished' label upon voting, when no other labels have been assigned. Or add an additional automatic 'Voted' label.
#29 by yorhel
2019-10-23 at 18:00
Labels. Not sure if that model is very intuitive though.
#30 by eacil
2019-10-23 at 21:26
Looks good to me. Not counter-intuitive. The only thing I could think of is the use of label you tag VN with instead of list you put VN in.

What built-in labels have special behavior? Blacklist and Voted? Wishlist is now just one category and it's up to us to create something similar to High/Medium/Low (while I guess they will be automatically converted)?

Is it possible to have notes. I really want to write the reasons why those VN are in my wishlist(S) as I forget all the time.Last modified on 2019-10-23 at 22:12
#31 by rampaa
2019-10-24 at 05:46
Will we be ever able to disclose our lists to certain individuals only? Like how AniDB lets you share your list with your "buddies"? It also lets you share it via a password, which is pretty neat as well.
#32 by yorhel
2019-10-24 at 07:25
What built-in labels have special behavior?
Only voted, for now. The others will probably have special behavior for compatibility with the VN filters, but not sure if that will stay or if I'll make the filters more generic.

Wishlist is now just one category and it's up to us to create something similar to High/Medium/Low (while I guess they will be automatically converted)?
Yes and yes.

Is it possible to have notes
It already is for the vnlist, with the merging of the wishlist/vnlist/votes I will also make the notes feature more prominent. Notes are public though, as they are now.

Will we be ever able to disclose our lists to certain individuals only?
No plans like that for this update.
#33 by yorhel
2019-10-29 at 13:25
Misadventures of an idiot who still doesn't know how to write UIs.
#34 by yorhel
2019-11-29 at 12:48
Does anyone even care about the ability to disable poll preview and re-casting poll votes? I'm inclined to just get rid of those options and always allow preview and recasting.

Reply

You must be logged in to reply to this thread.