Lemmy PostgreSQL

33 readers

2 users here now

founded 1 year ago

MODERATORS

[email protected]

PostgreSQL usage in Lemmy is many JOIN (bulletintree.com)

submitted 1 year ago by [email protected] to c/[email protected]

10 comments fedilink hide all child comments

Some learning about JOIN filled queries like the SELECT that is used to list post_aggregates table when browsing postings on Lemmy.

https://dba.stackexchange.com/questions/155972/postgres-join-conditions-vs-where-conditions

"Logically, it makes no difference at all whether you place conditions in the join clause of an INNER JOIN or the WHERE clause of the same SELECT. The effect is the same. (Not the case for OUTER JOIN!)"

Learning about join_collapse_limit

you are viewing a single comment's thread
view the rest of the comments

[–] [email protected] 1 points 1 year ago

And the difference between logged-in JOIN vs. anonymous logged-out JOIN count when the Rust code builds the query....

"If I remove any single one of the joins, the query time goes from 500ms to 1ms. With EXPLAIN I was able to see that Postgres is doing index only scans when joining 8 tables, but with 9 tables it starts doing sequential scans."