Conversation

Nov 6, 2021

This thread is astonishing. People know 1001 way to optimize a simple

query to a table with 11M rows running 3s — from migrating to ClickHouse to involving sharding And only a few understand what's btree and how it is working 🤷🏻‍♂️

Quote Tweet

James Long

@jlongster

Nov 6, 2021

Hitting scaling issues with PostgreSQL. Yes! The best kind of problem! Now I need to figure out how to make a query on 11644420 rows take less then 3 seconds.

Show this thread

147

Nikolay Samokhvalov

@samokhvalov

Nov 6, 2021

Disclaimer: I do hope this doesn't sound offensive to anyone. This is just a thought about lack of good materials that could improve average level of SQL optimization knowledge. Level is not good, we need better docs, talks, books etc. Maybe Postgres itself needs something.

Replying to

Maybe Postgres just need Marketing... I've read the twit and I don't understand what he want, I believe 3 seconds for 11M rows is good. Postgres maybe don't need more docs, talks, books and nice community, maybe, just need marketing...

Replying to

and

An index scan will locate the relevant point in the table very fast. 3 seconds might be fast if it was inherently necessary to look at all 11 million rows to get an answer, because of the specific query. But it's not, so index scan practically instant. That's the key takeaway.

11:34 PM · Nov 6, 2021Twitter Web App

Replying to

and

GIF

read image description

ALT

Replying to

and

Other thing is that this query wouldn't be that much slower with 10x or even 100x as many rows. But only if the query plan just has to "look stuff up" via same good index. Without that the slowdown as more data is added might be linear, or even worse.