Keep reading for FREE
A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed.
11
167 reads
PostgreSQL implements several types of indexes, such as btree, hash, gist, spgist. The default and most common type of index is btree. A btree (balanced tree) allows for easier and faster searching. This can be seen in the image above where we search for the key with the value of 53.
Btrees speed up searches because:
10
35 reads
When creating an index on the name field(for example), PostgreSQL saves all of them inside a B-Tree - keys are now represented by names. Each entry in the index consists of a C structure called IndexTupleData, and is followed by a bitmap and a value. Bitmaps record if any of the index attributes in a key are NULL, to save space.
Each IndexTupleData structure contains:
10
19 reads
Suppose we have an index on names and we query the following: SELECT * FROM users WHERE name = 'Captain Nemo' ORDER BY id ASC LIMIT 1. As you can see in the image, Postgre quickly narrows the results down until it finds the specific node where the name is located. A binary search algorithm is then used to quickly find the desired key in the node. The results are now easily fetched as the index points to the place where 'Captain Nemo' is located in memory.
10
13 reads
An example of an index scan is the earlier query. As there is an index on the user column and we asked for all the user details, after the value is found in the index, Postgre then goes to the heap and fetches the other fields.
An index-only scan means that all the needed information is found in the index and there is no need to go to memory. This is useful when querying a pair of fields multiple times - creating an index on those fields will output quicker results.
10
9 reads
If you only select a few rows, Postgre will decide on an index scan – if you select a majority of them, Postgre will decide on a table scan. But what if you read too much for an index scan to be efficient but too little for a sequential scan? The solution to the problem is to use a bitmap scan. This way, a single block is only used once during a scan. Postgre will first scan the index and compile those rows / blocks, which are needed at the end of the scan. Then Postgre will take this list and go to the table to really fetch those rows.
10
14 reads
Although indexes are useful as they provide faster searches, having too many of them could turn out to be a problem.
11
25 reads
More like this
1 idea
Foreign Key on Models | Django documentation
docs.djangoproject.com
4 ideas
What is Elasticsearch?
elastic.co
1 idea
Hands-on Tutorial on Python Data Processing Library Pandas – Part 1
datascienceplus.com
Explore the World’s
Best Ideas
Save ideas for later reading, for personalized stashes, or for remembering it later.
Start
31 ideas
Start
44 ideas
# Personal Growth
Take Your Ideas
Anywhere
Just press play and we take care of the words.
No Internet access? No problem. Within the mobile app, all your ideas are available, even when offline.
Ideas for your next work project? Quotes that inspire you? Put them in the right place so you never lose them.
Start
47 ideas
Start
75 ideas
My Stashes
Join
2 Million Stashers
4.8
5,740 Reviews
App Store
4.7
72,690 Reviews
Google Play
Shankul Varada
Best app ever! You heard it right. This app has helped me get back on my quest to get things done while equipping myself with knowledge everyday.
“
samz905
Don’t look further if you love learning new things. A refreshing concept that provides quick ideas for busy thought leaders.
“
Sean Green
Great interesting short snippets of informative articles. Highly recommended to anyone who loves information and lacks patience.
“
Ashley Anthony
This app is LOADED with RELEVANT, HELPFUL, AND EDUCATIONAL material. It is creatively intellectual, yet minimal enough to not overstimulate and create a learning block. I am exceptionally impressed with this app!
“
Jamyson Haug
Great for quick bits of information and interesting ideas around whatever topics you are interested in. Visually, it looks great as well.
“
Ghazala Begum
Even five minutes a day will improve your thinking. I've come across new ideas and learnt to improve existing ways to become more motivated, confident and happier.
“
Giovanna Scalzone
Brilliant. It feels fresh and encouraging. So many interesting pieces of information that are just enough to absorb and apply. So happy I found this.
“
Laetitia Berton
I have only been using it for a few days now, but I have found answers to questions I had never consciously formulated, or to problems I face everyday at work or at home. I wish I had found this earlier, highly recommended!
“
Read & Learn
20x Faster
without
deepstash
with
deepstash
with
deepstash
Access to 200,000+ ideas
—
Access to the mobile app
—
Unlimited idea saving & library
—
—
Unlimited history
—
—
Unlimited listening to ideas
—
—
Downloading & offline access
—
—
Personalized recommendations
—
—
Supercharge your mind with one idea per day
Enter your email and spend 1 minute every day to learn something new.
I agree to receive email updates