`FULL JOIN` implemented but 100x slower than Postgres: Performance footgun.

[samjewell]

Hey, I still love GMS - thanks for your work on it.

I naively started to use FULL JOIN to join a bunch of large tables recently. 5000 rows, FULL JOIN to 15000 rows, FULL JOIN again to 700 rows, FULL JOIN again to 200 rows.

This was painfully slow. (I eventually realised I could rewrite this by adding an additional CTE at the start, and then using LEFT JOINs instead).

I feel like I was given enough rope to hang myself with here. As:

• MySQL typically doesn't actually support FULL JOIN, and

EDIT: Postgres is 100x faster, even with no indices on the tables

• GMS doesn't use Indexes during querying

My feeling was that an efficient FULL JOIN would use Indexes (but maybe I'm wrong)

I think I'd suggest removing FULL JOIN from GMS, so that:

• It's more consistent with MySQL, and
• You remove this performance footgun, while indices are still not supported.

Do you think you could speed this up?

[angelamayxie]

@samjewell could you provide some examples of the queries you were using?

[samjewell]

traces_anon.csv
logs_anon.csv
metrics_this_month_anon.csv
teams_anon.csv

tele-cost-by-team-slimmed-down.SQL.txt

Hey @angelamayxie

I've attached a SQL query, and some CSV files which I run that query against.

Performance Comparison Results

Scenario	Duration	vs Postgres (12ms)	Slowdown Factor
Postgres baseline	12ms	-	1x
2-table FULL JOIN (teams + metrics + traces)	~1.45s	+1,438ms	121x slower
3-table FULL JOIN (teams + metrics + logs + traces)	~1.71s	+1,698ms	142x slower

GMS is much slower that Postgres. I get ~12ms in Postgres running this query even when I don't have any indices on my tables in Postgres. So the lack of indices here doesn't seem to be the issue - just slow performance I guess.

Go benchmark test

Can be found here: https://github.com/grafana/poc-sql-expressions-embedded-sql/pull/4/files#diff-eaa92ea8039d142b57f51fda5c81977db9c4e81add0f9dcd42a0968ff7c14070

[timsehn]

I'm going to move this to GMS since it is GMS specific.