Comments (8)
Hi there, I'd like to tackle this issue.
from keyboard-shortcuts-practice.
Thanks @Hausekey ! Please go through our contributing.md, then you can begin.
from keyboard-shortcuts-practice.
@Hausekey if you set DB_URL=postgres://localhost/your-db-name
in the .env
file or as an environment variable the app should connect to postgres and then you can proceed. HTH!
from keyboard-shortcuts-practice.
Yes @Hausekey I was thinking of a multi-column index. Intuitively, you'd think the DB would just combined indexes but IIRC it needs one for all of the WHERE or ORDER BY columns in order to make sure it uses an index fully. Obviously, our data isn't so big but it's more so we get snappy performance on the free DB tier. I think each important, commonly run query needs its own index in our case since the where clauses are different.
I think Sequelize should facilitate multi-column indexes but if you have any questions, lmk!
from keyboard-shortcuts-practice.
That looks great @Hausekey !
Please go ahead and make a PR when you get a chance.
from keyboard-shortcuts-practice.
Hi Alina and Bill,
I've followed through the contributing.md, and have been familiarizing myself with this project; I had a question about how to test the code with Postgres?
I believe I add the indexes code to orm.js, and I'd like to run some EXPLAIN queries, and report my findings. I see a dev-db.sqlite database, but haven't found any postgres ones. It's my first time working with Postgres
In the meantime, I can play around with the sqlite database.
from keyboard-shortcuts-practice.
Thanks @vegetabill , I got it to work! I found out how to generate dummy data for both users and user_answers, so I can run some EXPLAIN and do some benchmarks.
When you wrote "so it likely needs an ordered index on created_at along with user id and question number", were you thinking of creating multi-column indexes or would single-column suffice?
from keyboard-shortcuts-practice.
I see, to get the best performance on obtaining most recent progress, multi-column indexes are the way to go.
Below are some findings from the Postgres database that I wanted to report. I currently have two indexes on user_answers: created_at,user_id,question_number and created_at,user_id
To ensure that the indexes will be be used, what I've done was generate about 116 random users, and 198,000 rows of user answers, varying in different times and question numbers, thanks to the open source generatedata script. I picked out a random user_id and question number (e.g scudrural and 14)
- To get the most recent 3 answers by user and question number, my query was:
EXPLAIN ANALYZE SELECT * FROM "user_answers" WHERE
user_id = 'scudrural' AND question_number='14' ORDER BY created_at DESC limit 3;
Output:
Limit (cost=0.42..371.52 rows=3 width=34) (actual time=0.072..0.131 rows=3 loops=1)
-> Index Scan Backward using user_answers_created_at_user_id_question_number on user_answers (cost=0.42..6803.88 rows=55 width=34) (actual time=0.071..0.129 rows=3 loops=1)
" Index Cond: (((user_id)::text = 'scudrural'::text) AND (question_number = 14))"
Planning Time: 0.095 ms
Execution Time: 0.144 ms
- For most recent answer, the query was:
EXPLAIN ANALYZE SELECT * FROM "user_answers" WHERE
user_id = 'scudrural' ORDER BY created_at DESC limit 1;
Output:
-> Index Scan Backward using user_answers_created_at_user_id on user_answers (cost=0.42..10396.70 rows=1713 width=34) (actual time=0.029..0.029 rows=1 loops=1)
Limit (cost=0.42..6.49 rows=1 width=34) (actual time=0.029..0.030 rows=1 loops=1)
" Index Cond: ((user_id)::text = 'scudrural'::text)"
Planning Time: 0.128 ms
Execution Time: 0.047 ms
For comparison, output without the indexes, rerunning the 2nd query:
EXPLAIN ANALYZE SELECT * FROM "user_answers" WHERE
user_id = 'scudrural' ORDER BY created_at DESC limit 1;
Execution Time: 27.244 ms
Planning Time: 0.072 ms
Rows Removed by Filter: 198291
" Filter: ((user_id)::text = 'scudrural'::text)"
-> Seq Scan on user_answers (cost=0.00..4496.64 rows=1713 width=34) (actual time=0.029..26.625 rows=1601 loops=1)
Sort Method: top-N heapsort Memory: 25kB
Sort Key: created_at DESC
-> Sort (cost=4505.20..4509.49 rows=1713 width=34) (actual time=27.226..27.227 rows=1 loops=1)
Limit (cost=4505.20..4505.20 rows=1 width=34) (actual time=27.227..27.229 rows=1 loops=1)
I'm still learning how to read the output of the EXPLAIN ANALYZE query, but it looks like it's using the indexes!
Please let me know if there are questions or comments. When it looks good, I'll go ahead and make a pull request.
from keyboard-shortcuts-practice.
Related Issues (20)
- Bug: npm start cannot work on Windows HOT 4
- fix a vulnerability HOT 7
- [refactor] Replace repetitive if blocks with lookup HOT 3
- [jQuery] Replace jQuery.text function with native function HOT 3
- Delete unneeded files HOT 2
- Replace use of jQuery add/remove class HOT 2
- Replace window resizing listener with CSS HOT 2
- Test prod-like functionality
- [Refactor] Consolidate all API calls into an api-client.js file HOT 2
- Show Hint. After switch between questions, some keys still highlighted. HOT 2
- Fix jasmine API test authorization bug
- Deploy our new web app! HOT 5
- Add prettier support and enforce with commit hook HOT 2
- Add 'defer' attributes to script tags in index.ejs HOT 2
- Add a nice favicon HOT 17
- Setup webpack for the browser JavaScript HOT 2
- Replace jQuery ajax with fetch HOT 6
- Add ability to retrieve timing history for a single question HOT 10
- [jQuery] Replace CSS toggle class with built-in function HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from keyboard-shortcuts-practice.