Make your queries 43,240x faster

Indexes can greatly improve the performance of a Postgres database, especially as the size of the tables grow. In this video, Jon Meyers demonstrates how to create an index for a particular column, and use explain analyze to measure the optimization.
Managing Indexes in Postgres: supabase.com/docs/guides/data...
By default, Postgres creates a B-Tree index, which makes filtering result sets incredibly performant. The index stores a copy of the column's value, as well as a pointer to the full row, in case the query requires additional data.
00:00 Make your queries 43,240x faster
01:04 A very slow Select query (without index)
02:39 Create an Index
03:09 A super fast Select query (with index)
05:24 Selecting an Index - Postgres Query Planner
07:55 Does the order of where statements matter?
10:28 What happens if multiple indexes are available?
12:13 Why not index every column?
💻 Videos to watch next:
▶ 8 things you should NEVER do in a Database: • 8 things you should NE...
▶ FASTEST way to host a Postgres database (2024): • FASTEST way to host a ...
▶ Learn Postgres (playlist): • Learn Postgres
👇 Learn more about Supabase 👇
🕸 Website: supabase.com/
🏁 Get started: app.supabase.com/
📄 Docs: supabase.com/docs
🔔 Subscribe for more tutorials and feature updates from Supabase: / @supabase
📱 Connect with Us:
🐙 Github: www.github.com/supabase
💬 Discord: www.discord.supabase.com/
🐦 Twitter: / supabase
▶ Instagram (follow for memes): / supabasecom
ABOUT SUPABASE:
Supabase is the open source Firebase alternative. Supabase provides a full Postgres database for every project with pgvector, backups, realtime, and more. Add and manage email and password, passwordless, OAuth, and mobile logins to your project through a suite of identity providers and APIs.
Build in a weekend, scale to millions.
#Supabase #AppDevelopment #RealtimeApps #DeveloperTools

[@damnnn.]

SUPABASE is AWESOME. The Dev Team is teaching the product, how it should be. I am new to Supabase and these videos are very useful for me. If it's not important to you, try being overwhelmed with textwall documentations and useless videos.

[@mikelCold]

Amazing explainer for something so basic as indexes. I'm always surprised how can people learn this so late.
If there's another video I would recommend going into multiple column indexes too.

[@heyjitendra]

Thanks excited to know more about Indexes.

[@dshukertjr]

Loving the quote at the end!

[@TheBlackManMythLegend]

I used to work at EDF (electricite de France ) and we performed for PL/SQL script to update the 40 millions of line of customer to make the database faster. Kinda forgot about all of that. But that was satisfying to do.

[@aLfRemArShMeLlOw]

42069x faster

[@JonMeyers]

Missed opportunity! 😂

[@vincentjohnflorio]

Perfectly explained!

[@harryzachariou1]

Fantastic video!

[@activenode]

Nice one jon!

[@sire_ns]

Incredible! thanks

[@laugedyret]

Nice, thanks!

[@JonMeyers]

Glad you enjoyed it! 🙌

[@mabud_alam]

Please explain how the pgvector index works

[@roninspect4357]

will this index will work with iLike queries?

[@TheBlackManMythLegend]

very didactic

[@todoku1]

maybe a noob question but does the advantage of the postgres query planner also work when I use it from the client side like in flutter? like in 08:05

[@sumitpurohit8849]

Can you please update the docs for when to use which distance operator in HNSW and IVFFlat vector indexes. Currently it is unclear weather to use cosine distance, Euclidean distance or negative inner product. Also I would love to have detailed videos/blogs/docs on which embedding model to use for how many words(tokens) of data and also on hybrid search as well.

[@Dom-zy1qy]

Tbh this isn't really something you should be looking at the supabase documentation for.

[@tasfiqjawaad3757]

When I tried to index a table, it did improve the query time, but only by a couple of seconds. It was noticeable but not as much as in this video. Does it have anything to do with my supabase plan? I'm using the free tier.

[@poolkrooni]

How big is your dataset? In the video he's dealing with 10M entries

[@althafhussain9718]

In this video, the Explain Analyze tool reported a duration of 3 ms for the first query with username and is_active, and 0.02 ms for the second query with is_active and username. Can you explain why there is such a difference?

[@tylerhartman1630]

I would guess that either the machine is running multiple threads and the thread got swapped to inactive for 1 ms or that it cached the result from the first one, so it already knew the answer for the second one (because it processed it as the same query)

[@PramodGeorge]

Hot Damn!

[@GivSadds]

I left you database service , just because you don't have the query rows count features for the application codes . You have only in SQL but not in dart , javascript or any other languages

[@KieranCrown]

You can utilise DB triggers and functions to update the count and query it in the frontend with ease

[@Supabase]

You can get the row count using the count option: supabase.com/docs/reference/javascript/select
Also we have count() aggregate function option for counting rows of a referenced table: supabase.com/blog/postgrest-aggregate-functions#the-basics-of-aggregate-functions