https://supabase.com/ logo
Join Discord
Powered by
So as previously stated, I'm a noob in databases (...
# help
y
So as previously stated, I'm a noob in databases (here to learn ^^). I have a query with an inner join to grab some data from my supabase db. The table has "a lot" of rows (but not really, around 10k) . The query takes A LONG time to execute. What would be the best way to debug that. Details in thread
So here is the query. You can see I added some logging to see how long it takes to execute:
Copy code
const start = new Date();
    this.logger.debug(`Getting stats for summonerId: ${summonerId}`);
    const { data, error, status, count } = await this.supabaseClient
      .from('stats')
      .select('*, summoners!inner(summonerId)', { count: 'exact' })
      .eq('summoners.summonerId', summonerId)
      .eq('tftSetNumber', setNumber);
    this.logger.debug(
      `Found ${count} stats for summonerId: ${summonerId} in ${
        new Date().getTime() - start.getTime()
      }ms`,
    );
The logs:
Copy code
[Nest] 81890  - 02/28/2022, 11:44:39 PM   DEBUG [SupabaseClientWrapper] Found 290 stats for summonerId: 2tHqjDf8Fu9n7wugb4kovjM5VMnrk26-7wZ3plHjaSotgJM in 58159ms
[Nest] 81890  - 02/28/2022, 11:44:39 PM   DEBUG [SupabaseClientWrapper] Found 1464 stats for summonerId: Bi_S-DA3WoHIuO2sG_edqx5kMcaZKZ2oyUOm4kjfnXbRo1U in 58617ms
[Nest] 81890  - 02/28/2022, 11:45:37 PM   DEBUG [SupabaseClientWrapper] Found 2459 stats for summonerId: FDyWNEBNF07CzNSmSI-_ajFAvfpfha55gTfpuDp_2iqmuMJk in 116528ms
[Nest] 81890  - 02/28/2022, 11:45:38 PM   DEBUG [SupabaseClientWrapper] Found 3868 stats for summonerId: mPkobFr3lC-BvzF49lIOipqNNtkyv35Or4SbKCncCOapZJHI in 117555ms
[Nest] 81890  - 02/28/2022, 11:45:39 PM   DEBUG [SupabaseClientWrapper] Found 181 stats for summonerId: i-nTge0Tkh8HQKoeB8aBZ1ghjkNvoH1DY7F4728K-KHNcQxH in 118866ms
n
Can you paste the table schema?
y
The 
stats
table?
Copy code
id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "matchId" text COLLATE pg_catalog."default" NOT NULL,
    level bigint NOT NULL,
    placement bigint NOT NULL,
    "totalDamageToPlayers" bigint,
    "goldLeft" bigint NOT NULL,
    "playersEliminated" bigint NOT NULL,
    "tftSetNumber" bigint NOT NULL,
    CONSTRAINT stats_pkey PRIMARY KEY (id),
    CONSTRAINT stats_id_key UNIQUE (id)
n
and you're joining against something else?
y
Yep
Copy code
CREATE TABLE IF NOT EXISTS public.stat_summoner
(
    id bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    "summonerId" text COLLATE pg_catalog."default" NOT NULL,
    "statId" bigint NOT NULL,
    CONSTRAINT stat_summoner_pkey PRIMARY KEY (id),
    CONSTRAINT "stat_summoner_statId_fkey" FOREIGN KEY ("statId")
        REFERENCES public.stats (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT "stat_summoner_summonerId_fkey" FOREIGN KEY ("summonerId")
        REFERENCES public.summoners ("summonerId") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
n
ok i'm not great with postgres but i suspect the 
text
type of 
summonerId
could be causing issues
have you tried connecting directly and working out the query in plain sql?
you might be able to spot where the indexing problem is a little easier that way
y
No, will do, that's probably the best way. Thanks for the help 🙂
Well
21ms in the query editor (inner wasn't necessary)
Now the funny thing sometimes it seems to really struggle when I call it from the js sdk, and that happens:
Copy code
[Nest] 85345  - 03/01/2022, 12:25:44 AM   ERROR [SupabaseClientWrapper] Object:
{
  "message": "Database client error. Retrying the connection.",
  "details": "no connection to the server\n"
}
n
dumb question, but 
from stats, summoners
- what is the join key? how does it figure that out for you?
i expected to see something like 
from stats join summoners on stats.id = summoners.stats_id
or something
y
@User not dumb at all. What @User told me a few days ago is that under the hood it knows to find the join table (stat_summoner): https://postgrest.org/en/v9.0/api.html?highlight=relationships#embedding-through-join-tables
n
oh, gotcha i didn't realize there was a join table
y
Fwiw, I moved all my migrations to ley, wrote them by hand. Instead of using the migrations generated by the supabase-cli which are weird looking and have a lot of things. Things seem to be a lot faster. Not sure what was the issue. Though I resetted my DB so we'll see when it has a lot more content I guess...
PreviousNext
Powered by