Help writing with #explain syntax

[fakefarm]

Hello,

I am running #insert and it runs well for certain rows, but not others. My DB connection is:

 @db_primary ||= Sequel.connect(adapter: :snowflake, drvconnect: PRIMARY_DB)

table = @db_primary.from(:people).order(:first_name)

table.insert(person)

I would like to run #explain on an #insert, but I'm not entirelly sure how to get to #explain. My research came up with Dataset#explain, but I am unable to figure out how to access it. I have tried various forms of:

table.explain(insert)
table.insert().explain 
@db_primary.explain

I am requesting help writing #explain syntax, and/or being pointed in the direction that can help me do so.

Thank you,
Dave

[rgalanakis]

I'm sure someone can get a better answer, but you can also do puts table.insert_sql to get the raw SQL and then EXPLAIN it in your SQL tool of choice. I am not sure if there is a way to get a dataset for the explain (but you could probably tell by digging into source a bit).

[fakefarm]

Thank you @rgalanakis, that helped!

[jeremyevans]

For this, you can use table.with_sql(:insert_sql, person).explain

[fakefarm]

Hi @jeremyevans,

(I'm a big fan of your work!)

When I try what you've suggested...

table.with_sql(:insert_sql, person).explain

I get the following no method error..

NoMethodError: undefined method `explain' for #<Sequel::Snowflake::Dataset: "INSERT INTO \"PEOPLE\" (#)">

Is it possible that the gem driver I'm using sequel-snowflake is not compliant with a sequel driver API for implementing explain, or delegating to it? (Thank you, @kwong-yw, for creating that gem!) If so, I'd be open to supplying a patch to that gem if I can understand out how to apply #explain to it.

Thanks for all your work!
Dave

[adam12]

It's most likely a driver issue, as you can simulate the command with the mock postgres driver without issue.

$ sequel -E mock://postgres -c 'DB[:foo].with_sql(:insert_sql, "bar").explain'
I, [2022-05-20T20:13:20.426228 #18746]  INFO -- : (0.000000s) EXPLAIN INSERT INTO "foo" VALUES ('bar')

[jeremyevans]

Dataset#explain is only implemented in a few Sequel adapters. You can probably look at the implementation in the postgres adapter, and use a similar approach in Snowflake, assuming that this is something Snowflake supports.

[kwong-yw]

Just closing the loop here, I've implemented #explain in sequel-snowflake v2.1.0 so something like table.with_sql(:insert_sql, person).explain should work now 👍