PgHero
No long running queries
Connections healthy 21
Vacuuming healthy
No columns near integer overflow
No invalid indexes or constraints
No duplicate indexes
No suggested indexes
23 slow queries

Slow Queries

Slow queries take 20 ms or more on average and have been called at least 100 times.

Explain queries to see where to add indexes.

Total Time Average Time Calls
17,837 min 54% 2,265 ms 472,545 hasura
SELECT  "_subs"."result_id" , "_fld_resp"."root" AS "result" FROM UNNEST(($1)::uuid[], ($2)::json[]) AS "_subs"("result_id", "result_vars") LEFT OUTER JOIN LATERAL (SELECT  json_build_object($3, "node_block"."root" ) AS "root" FROM  (SELECT  coalesce(json_agg("root" ORDER BY "root.or.block.pg.height" DESC NULLS FIRST), $4 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.or.block"."block" AS "block", $5 AS "__typename"       ) AS "_e"      ) ) AS "root", "_root.or.block"."root.or.block.pg.height" AS "root.or.block.pg.height" FROM  (SELECT  *  FROM "public"."node_block"  WHERE (EXISTS  (SELECT  $6  FROM "public"."node" AS "__be_0_node" WHERE (((("__be_0_node"."internal_id") = ("public"."node_block"."node_internal_id")) AND ($7)) AND (($8) AND ((((("__be_0_node"."name") ~ ((("_subs"."result_vars"#>>ARRAY[$9, $10]))::text)) AND ($11)) AND ($12)) AND ($13))))     ))     ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("_root.or.block.base"."height")::text AS "height", $14 AS "__typename"       ) AS "_e"      ) ) AS "block", "_root.or.block.base"."height" AS "root.or.block.pg.height" FROM  (SELECT  *  FROM "public"."block"  WHERE (("_root.base"."block_internal_id") = ("internal_id"))    LIMIT $15 ) AS "_root.or.block.base"      ) AS "_root.or.block" ON ($16)    ORDER BY "root.or.block.pg.height" DESC NULLS FIRST LIMIT $17 ) AS "_root"      ) AS "node_block"      ) AS "_fld_resp" ON ($18)
3,130 min 9% 12,940 ms 14,512 chaingraph
WITH accepted_transactions AS (
	SELECT node_internal_id, transaction_internal_id, accepted_at FROM block_transaction INNER JOIN new_table ON block_transaction.block_internal_id = new_table.block_internal_id
),
newly_spent AS (
	SELECT accepted_transactions.node_internal_id, accepted_transactions.transaction_internal_id, accepted_transactions.accepted_at, input.outpoint_index
		FROM input INNER JOIN accepted_transactions ON input.transaction_internal_id = accepted_transactions.transaction_internal_id
),
accepted_and_replaced_transactions AS (
    SELECT newly_spent.node_internal_id, input.transaction_internal_id, CASE WHEN input.transaction_internal_id != newly_spent.transaction_internal_id THEN newly_spent.accepted_at ELSE $1 END AS replaced_at
        FROM input INNER JOIN newly_spent ON input.transaction_internal_id = newly_spent.transaction_internal_id AND input.outpoint_index = newly_spent.outpoint_index
        WHERE input.outpoint_transaction_hash != $2::bytea
--        GROUP BY newly_spent.node_internal_id, input.transaction_internal_id, replaced -- doesn't improve performance
),
deleted_node_transactions AS (
    DELETE FROM node_transaction
        USING accepted_and_replaced_transactions
        WHERE node_transaction.node_internal_id = accepted_and_replaced_transactions.node_internal_id
            AND node_transaction.transaction_internal_id = accepted_and_replaced_transactions.transaction_internal_id
        RETURNING node_transaction.node_internal_id, node_transaction.transaction_internal_id, validated_at, accepted_and_replaced_transactions.replaced_at
)
INSERT INTO node_transaction_history (node_internal_id, transaction_internal_id, validated_at, replaced_at)
	SELECT node_internal_id, transaction_internal_id, validated_at, replaced_at FROM deleted_node_transactions
2,656 min 8% 354 ms 450,146 hasura
SELECT  coalesce(json_agg("root" ), $3 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."hash" AS "hash", "_root.ar.root.authchains"."authchains" AS "authchains"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("public"."transaction"."hash") = (($2)::bytea))    LIMIT $4 ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."authchains" ), $5 )  FROM  (SELECT  "_unnest_table"."authchains" AS "authchains" FROM UNNEST(array_agg("authchains" )) AS "_unnest_table"("authchains")     LIMIT $6 ) AS "_sub_query"      ) AS "authchains" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.or.authhead"."authhead" AS "authhead", "_root.ar.root.authchains.base"."authchain_length" AS "authchain_length"       ) AS "_e"      ) ) AS "authchains" FROM  (SELECT  *  FROM "public"."authchain_view"  WHERE (("_root.base"."internal_id") = ("transaction_internal_id"))     ) AS "_root.ar.root.authchains.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.or.authhead.base"."hash" AS "hash", "_root.ar.root.authchains.or.authhead.cf.identity_output"."identity_output" AS "identity_output"       ) AS "_e"      ) ) AS "authhead" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("_root.ar.root.authchains.base"."authhead_transaction_hash") = ("hash"))    LIMIT $7 ) AS "_root.ar.root.authchains.or.authhead.base" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("identity_output" ), $8 ) AS "identity_output" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("_root.ar.root.authchains.or.authhead.cf.identity_output.base"."fungible_token_amount")::text AS "fungible_token_amount"       ) AS "_e"      ) ) AS "identity_output" FROM  (SELECT  *  FROM "public"."transaction_identity_output"("_root.ar.root.authchains.or.authhead.base") AS "_transaction_identity_output" WHERE ($9)    LIMIT $10 ) AS "_root.ar.root.authchains.or.authhead.cf.identity_output.base"      ) AS "_root.ar.root.authchains.or.authhead.cf.identity_output"      ) AS "_root.ar.root.authchains.or.authhead.cf.identity_output" ON ($11)      ) AS "_root.ar.root.authchains.or.authhead" ON ($12)      ) AS "_root.ar.root.authchains"      ) AS "_root.ar.root.authchains" ON ($13)      ) AS "_root"
1,612 min 5% 1,503 ms 64,353 hasura
SELECT  coalesce(json_agg("root" ), $7 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."hash" AS "hash", "_root.ar.root.authchains"."authchains" AS "authchains"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."transaction"  WHERE ((EXISTS  (SELECT  $8  FROM "public"."block_transaction" AS "__be_0_block_transaction" WHERE (((("__be_0_block_transaction"."transaction_internal_id") = ("public"."transaction"."internal_id")) AND ($9)) AND (($10) AND (((EXISTS  (SELECT  $11  FROM "public"."block" AS "__be_1_block" WHERE (((("__be_1_block"."internal_id") = ("__be_0_block_transaction"."block_internal_id")) AND ($12)) AND (($13) AND (((EXISTS  (SELECT  $14  FROM "public"."node_block" AS "__be_2_node_block" WHERE (((("__be_2_node_block"."block_internal_id") = ("__be_1_block"."internal_id")) AND ($15)) AND (($16) AND (((EXISTS  (SELECT  $17  FROM "public"."node" AS "__be_3_node" WHERE (((("__be_3_node"."internal_id") = ("__be_2_node_block"."node_internal_id")) AND ($18)) AND (($19) AND ((((("__be_3_node"."name") ILIKE (($5)::text)) AND ($20)) AND ($21)) AND ($22))))     )) AND ($23)) AND ($24))))     )) AND ($25)) AND ($26))))     )) AND ($27)) AND ($28))))     )) AND (("public"."transaction"."hash") = (($6)::bytea)))    LIMIT $29 ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."authchains" ), $30 )  FROM  (SELECT  "_unnest_table"."authchains" AS "authchains" FROM UNNEST(array_agg("authchains" )) AS "_unnest_table"("authchains")     LIMIT $31 ) AS "_sub_query"      ) AS "authchains" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.base"."authchain_length" AS "authchain_length", "_root.ar.root.authchains.ar.authchains.migrations"."migrations" AS "migrations"       ) AS "_e"      ) ) AS "authchains" FROM  (SELECT  *  FROM "public"."authchain_view"  WHERE (("_root.base"."internal_id") = ("transaction_internal_id"))     ) AS "_root.ar.root.authchains.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."migrations" ), $32 )  FROM  (SELECT  "_unnest_table"."migrations" AS "migrations" FROM UNNEST(array_agg("migrations" )) AS "_unnest_table"("migrations")     LIMIT $33 ) AS "_sub_query"      ) AS "migrations" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction"."transaction" AS "transaction"       ) AS "_e"      ) ) AS "migrations" FROM  (SELECT  *  FROM "public"."authchain_migrations_view"  WHERE ((("_root.ar.root.authchains.base"."transaction_internal_id") = ("authbase_internal_id")) AND (EXISTS  (SELECT  $34  FROM "public"."authchain_migration_transaction"("public"."authchain_migrations_view".*) AS "__be_0_authchain_migration_transaction" WHERE ((EXISTS  (SELECT  $35  FROM "public"."output" AS "__be_1_output" WHERE (((("__be_1_output"."transaction_hash") = ("__be_0_authchain_migration_transaction"."hash")) AND ($36)) AND (($37) AND ((((("public"."output_locking_bytecode_pattern"("__be_1_output".*) ) LIKE (($4)::text)) AND ($38)) AND ($39)) AND ($40))))     )) AND ($41))     )))     ) AS "_root.ar.root.authchains.ar.authchains.migrations.base" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("transaction" ), $42 ) AS "transaction" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash" AS "hash", "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"."inputs" AS "inputs", "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"."outputs" AS "outputs"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."authchain_migration_transaction"("_root.ar.root.authchains.ar.authchains.migrations.base") AS "_authchain_migration_transaction" WHERE ($43)    LIMIT $44 ) AS "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."outputs" ), $45 )  FROM  (SELECT  "_unnest_table"."outputs" AS "outputs" FROM UNNEST(array_agg("outputs" )) AS "_unnest_table"("outputs")     LIMIT $46 ) AS "_sub_query"      ) AS "outputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."output_index")::text AS "output_index", "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."locking_bytecode" AS "locking_bytecode"       ) AS "_e"      ) ) AS "outputs" FROM  (SELECT  *  FROM "public"."output"  WHERE ((("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash") = ("transaction_hash")) AND (("public"."output_locking_bytecode_pattern"("public"."output".*) ) LIKE (($3)::text)))     ) AS "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs" ON ($47) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."inputs" ), $48 )  FROM  (SELECT  "_unnest_table"."inputs" AS "inputs" FROM UNNEST(array_agg("inputs" )) AS "_unnest_table"("inputs")     LIMIT $49 ) AS "_sub_query"      ) AS "inputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"."outpoint_index")::text AS "outpoint_index"       ) AS "_e"      ) ) AS "inputs" FROM  (SELECT  *  FROM "public"."input"  WHERE ((("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."internal_id") = ("transaction_internal_id")) AND (("public"."input"."outpoint_index") = (($2)::bigint)))     ) AS "md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs" ON ($50)      ) AS "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction"      ) AS "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction" ON ($51)      ) AS "_root.ar.root.authchains.ar.authchains.migrations"      ) AS "_root.ar.root.authchains.ar.authchains.migrations" ON ($52)      ) AS "_root.ar.root.authchains"      ) AS "_root.ar.root.authchains" ON ($53)      ) AS "_root"
850 min 3% 675 ms 75,593 hasura
SELECT  coalesce(json_agg("root" ORDER BY "root.or.transaction.pg.internal_id" DESC NULLS FIRST), $5 ) AS "root" FROM  (SELECT  "_root.or.transaction"."root.or.transaction.pg.internal_id" AS "root.or.transaction.pg.internal_id", row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."locking_bytecode" AS "locking_bytecode", ("_root.base"."output_index")::text AS "output_index", "_root.base"."transaction_hash" AS "transaction_hash", "_root.or.transaction"."transaction" AS "transaction"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."search_output_prefix"(($4)::text) AS "_search_output_prefix" WHERE ($6)     ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  "_root.or.transaction.base"."internal_id" AS "root.or.transaction.pg.internal_id", row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.or.transaction.ar.transaction.outputs"."outputs" AS "outputs"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("_root.base"."transaction_hash") = ("hash"))    LIMIT $7 ) AS "_root.or.transaction.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."outputs" ), $8 )  FROM  (SELECT  "_unnest_table"."outputs" AS "outputs" FROM UNNEST(array_agg("outputs" )) AS "_unnest_table"("outputs")     LIMIT $9 ) AS "_sub_query"      ) AS "outputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("_root.or.transaction.ar.transaction.outputs.base"."output_index")::text AS "output_index", "_root.or.transaction.ar.transaction.outputs.base"."locking_bytecode" AS "locking_bytecode", ("_root.or.transaction.ar.transaction.outputs.base"."value_satoshis")::text AS "value_satoshis", "_root.or.transaction.ar.transaction.outputs.base"."token_category" AS "token_category", ("_root.or.transaction.ar.transaction.outputs.base"."fungible_token_amount")::text AS "fungible_token_amount", "_root.or.transaction.ar.transaction.outputs.base"."nonfungible_token_capability" AS "nonfungible_token_capability", "_root.or.transaction.ar.transaction.outputs.base"."nonfungible_token_commitment" AS "nonfungible_token_commitment", "_root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by"."spent_by" AS "spent_by"       ) AS "_e"      ) ) AS "outputs" FROM  (SELECT  *  FROM "public"."output"  WHERE ((("_root.or.transaction.base"."hash") = ("transaction_hash")) AND ((EXISTS  (SELECT  $10  FROM "public"."input" AS "__be_0_input" WHERE (((("__be_0_input"."outpoint_transaction_hash") = ("public"."output"."transaction_hash")) AND ((("__be_0_input"."outpoint_index") = ("public"."output"."output_index")) AND ($11))) AND (($12) AND (($13) AND ($14))))     )) AND (("public"."output"."output_index") = (($3)::bigint))))     ) AS "_root.or.transaction.ar.transaction.outputs.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."spent_by" ), $15 )  FROM  (SELECT  "_unnest_table"."spent_by" AS "spent_by" FROM UNNEST(array_agg("spent_by" )) AS "_unnest_table"("spent_by")     LIMIT $16 ) AS "_sub_query"      ) AS "spent_by" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_3af85ef6759567edc35d019973cef70b__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction"."transaction" AS "transaction"       ) AS "_e"      ) ) AS "spent_by" FROM  (SELECT  *  FROM "public"."input"  WHERE ((("_root.or.transaction.ar.transaction.outputs.base"."transaction_hash") = ("outpoint_transaction_hash")) AND (("_root.or.transaction.ar.transaction.outputs.base"."output_index") = ("outpoint_index")))     ) AS "md5_c69a32c7249ae273493b855f08ee549c__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_181392f418616e550665e586cb3dbc16__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.block_inclusions"."block_inclusions" AS "block_inclusions", ("md5_198b85e60ca014a5b676ba4d2eb441b1__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.base"."internal_id")::text AS "internal_id", "md5_198b85e60ca014a5b676ba4d2eb441b1__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.base"."hash" AS "hash", "md5_1b3260a66a35d300a3d7ac7e3c3347a5__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.outputs"."outputs" AS "outputs"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("md5_c69a32c7249ae273493b855f08ee549c__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.base"."transaction_internal_id") = ("internal_id"))    LIMIT $17 ) AS "md5_198b85e60ca014a5b676ba4d2eb441b1__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."outputs" ), $18 )  FROM  (SELECT  "_unnest_table"."outputs" AS "outputs" FROM UNNEST(array_agg("outputs" )) AS "_unnest_table"("outputs")     LIMIT $19 ) AS "_sub_query"      ) AS "outputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_860acf75867510eadfd27a325f171812__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.outputs.base"."locking_bytecode" AS "locking_bytecode"       ) AS "_e"      ) ) AS "outputs" FROM  (SELECT  *  FROM "public"."output"  WHERE ((("md5_198b85e60ca014a5b676ba4d2eb441b1__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.base"."hash") = ("transaction_hash")) AND (("public"."output"."output_index") = ANY(($2)::bigint[])))     ) AS "md5_860acf75867510eadfd27a325f171812__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.outputs.base"      ) AS "md5_1b3260a66a35d300a3d7ac7e3c3347a5__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.outputs"      ) AS "md5_1b3260a66a35d300a3d7ac7e3c3347a5__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.outputs" ON ($20) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."block_inclusions" ), $21 )  FROM  (SELECT  "_unnest_table"."block_inclusions" AS "block_inclusions" FROM UNNEST(array_agg("block_inclusions" )) AS "_unnest_table"("block_inclusions")     LIMIT $22 ) AS "_sub_query"      ) AS "block_inclusions" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_5929a96f9409d321c13410a3ca4f8c1d__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.block_inclusions.or.block"."block" AS "block"       ) AS "_e"      ) ) AS "block_inclusions" FROM  (SELECT  *  FROM "public"."block_transaction"  WHERE (("md5_198b85e60ca014a5b676ba4d2eb441b1__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.base"."internal_id") = ("transaction_internal_id"))     ) AS "md5_635f8bcc7d4d5d7954ca7598199b70ed__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.block_inclusions.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("md5_7eded9211c0c5b18502ad01279455113__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.block_inclusions.or.block.base"."timestamp")::text AS "timestamp"       ) AS "_e"      ) ) AS "block" FROM  (SELECT  *  FROM "public"."block"  WHERE (("md5_635f8bcc7d4d5d7954ca7598199b70ed__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.block_inclusions.base"."block_internal_id") = ("internal_id"))    LIMIT $23 ) AS "md5_7eded9211c0c5b18502ad01279455113__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.block_inclusions.or.block.base"      ) AS "md5_5929a96f9409d321c13410a3ca4f8c1d__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.block_inclusions.or.block" ON ($24)      ) AS "md5_181392f418616e550665e586cb3dbc16__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.block_inclusions"      ) AS "md5_181392f418616e550665e586cb3dbc16__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.ar.transaction.block_inclusions" ON ($25)      ) AS "md5_3af85ef6759567edc35d019973cef70b__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction" ON ($26)      ) AS "_root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by"      ) AS "_root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by" ON ($27)      ) AS "_root.or.transaction.ar.transaction.outputs"      ) AS "_root.or.transaction.ar.transaction.outputs" ON ($28)      ) AS "_root.or.transaction" ON ($29)    ORDER BY "root.or.transaction.pg.internal_id" DESC NULLS FIRST LIMIT $30 ) AS "_root"
633 min 2% 222 ms 170,968 hasura
SELECT  "_subs"."result_id" , "_fld_resp"."root" AS "result" FROM UNNEST(($1)::uuid[], ($2)::json[]) AS "_subs"("result_id", "result_vars") LEFT OUTER JOIN LATERAL (SELECT  json_build_object($3, "block"."root" ) AS "root" FROM  (SELECT  coalesce(json_agg("root" ORDER BY "root.pg.height" DESC NULLS FIRST), $4 ) AS "root" FROM  (SELECT  "_root.base"."height" AS "root.pg.height", row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."hash" AS "hash", ("_root.base"."height")::text AS "height", ("public"."block_transaction_count"("_root.base") )::text AS "transaction_count", ("public"."block_input_count"("_root.base") )::text AS "input_count", ("public"."block_output_count"("_root.base") )::text AS "output_count", ("public"."block_output_value_satoshis"("_root.base") )::text AS "output_value_satoshis", ("_root.base"."size_bytes")::text AS "size_bytes", ("public"."block_generated_value_satoshis"("_root.base") )::text AS "generated_value_satoshis", ("_root.base"."timestamp")::text AS "timestamp", $5 AS "__typename"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."block"  WHERE (EXISTS  (SELECT  $6  FROM "public"."node_block" AS "__be_0_node_block" WHERE (((("__be_0_node_block"."block_internal_id") = ("public"."block"."internal_id")) AND ($7)) AND (($8) AND (((EXISTS  (SELECT  $9  FROM "public"."node" AS "__be_1_node" WHERE (((("__be_1_node"."internal_id") = ("__be_0_node_block"."node_internal_id")) AND ($10)) AND (($11) AND ((((("__be_1_node"."name") ~ ((("_subs"."result_vars"#>>ARRAY[$12, $13]))::text)) AND ($14)) AND ($15)) AND ($16))))     )) AND ($17)) AND ($18))))     ))   ORDER BY "height" DESC NULLS FIRST LIMIT $19 OFFSET $20) AS "_root.base"    ORDER BY "root.pg.height" DESC NULLS FIRST  ) AS "_root"      ) AS "block"      ) AS "_fld_resp" ON ($21)
539 min 2% 427 ms 75,744 hasura
SELECT  coalesce(json_agg("root" ORDER BY "root.or.transaction.pg.internal_id" DESC NULLS FIRST), $4 ) AS "root" FROM  (SELECT  "_root.or.transaction"."root.or.transaction.pg.internal_id" AS "root.or.transaction.pg.internal_id", row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."locking_bytecode" AS "locking_bytecode", ("_root.base"."output_index")::text AS "output_index", "_root.base"."transaction_hash" AS "transaction_hash", "_root.or.transaction"."transaction" AS "transaction"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."search_output_prefix"(($3)::text) AS "_search_output_prefix" WHERE ($5)     ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  "_root.or.transaction.base"."internal_id" AS "root.or.transaction.pg.internal_id", row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.or.transaction.ar.transaction.outputs"."outputs" AS "outputs"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("_root.base"."transaction_hash") = ("hash"))    LIMIT $6 ) AS "_root.or.transaction.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."outputs" ), $7 )  FROM  (SELECT  "_unnest_table"."outputs" AS "outputs" FROM UNNEST(array_agg("outputs" )) AS "_unnest_table"("outputs")     LIMIT $8 ) AS "_sub_query"      ) AS "outputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("_root.or.transaction.ar.transaction.outputs.base"."output_index")::text AS "output_index", "_root.or.transaction.ar.transaction.outputs.base"."locking_bytecode" AS "locking_bytecode", ("_root.or.transaction.ar.transaction.outputs.base"."value_satoshis")::text AS "value_satoshis", "_root.or.transaction.ar.transaction.outputs.base"."token_category" AS "token_category", ("_root.or.transaction.ar.transaction.outputs.base"."fungible_token_amount")::text AS "fungible_token_amount", "_root.or.transaction.ar.transaction.outputs.base"."nonfungible_token_capability" AS "nonfungible_token_capability", "_root.or.transaction.ar.transaction.outputs.base"."nonfungible_token_commitment" AS "nonfungible_token_commitment", "_root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by"."spent_by" AS "spent_by"       ) AS "_e"      ) ) AS "outputs" FROM  (SELECT  *  FROM "public"."output"  WHERE ((("_root.or.transaction.base"."hash") = ("transaction_hash")) AND ((("public"."output"."output_index") = (($2)::bigint)) AND (NOT ((EXISTS  (SELECT  $9  FROM "public"."input" AS "__be_0_input" WHERE (((("__be_0_input"."outpoint_transaction_hash") = ("public"."output"."transaction_hash")) AND ((("__be_0_input"."outpoint_index") = ("public"."output"."output_index")) AND ($10))) AND (($11) AND (($12) AND ($13))))     )) AND ($14)))))     ) AS "_root.or.transaction.ar.transaction.outputs.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."spent_by" ), $15 )  FROM  (SELECT  "_unnest_table"."spent_by" AS "spent_by" FROM UNNEST(array_agg("spent_by" )) AS "_unnest_table"("spent_by")     LIMIT $16 ) AS "_sub_query"      ) AS "spent_by" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_3af85ef6759567edc35d019973cef70b__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction"."transaction" AS "transaction"       ) AS "_e"      ) ) AS "spent_by" FROM  (SELECT  *  FROM "public"."input"  WHERE ((("_root.or.transaction.ar.transaction.outputs.base"."transaction_hash") = ("outpoint_transaction_hash")) AND (("_root.or.transaction.ar.transaction.outputs.base"."output_index") = ("outpoint_index")))     ) AS "md5_c69a32c7249ae273493b855f08ee549c__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_198b85e60ca014a5b676ba4d2eb441b1__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.base"."hash" AS "hash"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("md5_c69a32c7249ae273493b855f08ee549c__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.base"."transaction_internal_id") = ("internal_id"))    LIMIT $17 ) AS "md5_198b85e60ca014a5b676ba4d2eb441b1__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction.base"      ) AS "md5_3af85ef6759567edc35d019973cef70b__root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by.or.transaction" ON ($18)      ) AS "_root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by"      ) AS "_root.or.transaction.ar.transaction.outputs.ar.outputs.spent_by" ON ($19)      ) AS "_root.or.transaction.ar.transaction.outputs"      ) AS "_root.or.transaction.ar.transaction.outputs" ON ($20)      ) AS "_root.or.transaction" ON ($21)    ORDER BY "root.or.transaction.pg.internal_id" DESC NULLS FIRST LIMIT $22 ) AS "_root"
472 min 1% 796 ms 35,569 hasura
SELECT  coalesce(json_agg("root" ), $6 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."hash" AS "hash", "_root.ar.root.authchains"."authchains" AS "authchains"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("public"."transaction"."hash") = ANY(($5)::bytea[]))    LIMIT $7 ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."authchains" ), $8 )  FROM  (SELECT  "_unnest_table"."authchains" AS "authchains" FROM UNNEST(array_agg("authchains" )) AS "_unnest_table"("authchains")     LIMIT $9 ) AS "_sub_query"      ) AS "authchains" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.base"."authchain_length" AS "authchain_length", "_root.ar.root.authchains.ar.authchains.migrations"."migrations" AS "migrations"       ) AS "_e"      ) ) AS "authchains" FROM  (SELECT  *  FROM "public"."authchain_view"  WHERE (("_root.base"."internal_id") = ("transaction_internal_id"))     ) AS "_root.ar.root.authchains.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."migrations" ORDER BY "ob_col_1" DESC NULLS FIRST), $10 )  FROM  (SELECT  "_unnest_table"."migrations" AS "migrations", "_unnest_table"."ob_col_1" AS "ob_col_1" FROM UNNEST(array_agg("migrations" ), array_agg("md5_3e9df6841b24f1f40a5034f7fc1c5d8d_root.ar.root.authchains.ar.authchains.migrations.pg.migration_index" )) AS "_unnest_table"("migrations", "ob_col_1")    ORDER BY "ob_col_1" DESC NULLS FIRST LIMIT $11 ) AS "_sub_query"      ) AS "migrations" FROM  (SELECT  "_root.ar.root.authchains.ar.authchains.migrations.base"."migration_index" AS "md5_3e9df6841b24f1f40a5034f7fc1c5d8d_root.ar.root.authchains.ar.authchains.migrations.pg.migration_index", row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction"."transaction" AS "transaction"       ) AS "_e"      ) ) AS "migrations" FROM  (SELECT  *  FROM "public"."authchain_migrations_view"  WHERE ((("_root.ar.root.authchains.base"."transaction_internal_id") = ("authbase_internal_id")) AND (EXISTS  (SELECT  $12  FROM "public"."authchain_migration_transaction"("public"."authchain_migrations_view".*) AS "__be_0_authchain_migration_transaction" WHERE ((EXISTS  (SELECT  $13  FROM "public"."output" AS "__be_1_output" WHERE (((("__be_1_output"."transaction_hash") = ("__be_0_authchain_migration_transaction"."hash")) AND ($14)) AND (($15) AND ((((("public"."output_locking_bytecode_pattern"("__be_1_output".*) ) LIKE (($4)::text)) AND ($16)) AND ($17)) AND ($18))))     )) AND ($19))     )))   ORDER BY "migration_index" DESC NULLS FIRST LIMIT $20 ) AS "_root.ar.root.authchains.ar.authchains.migrations.base" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("transaction" ), $21 ) AS "transaction" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash" AS "hash", "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"."inputs" AS "inputs", "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"."outputs" AS "outputs"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."authchain_migration_transaction"("_root.ar.root.authchains.ar.authchains.migrations.base") AS "_authchain_migration_transaction" WHERE ($22)    LIMIT $23 ) AS "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."outputs" ), $24 )  FROM  (SELECT  "_unnest_table"."outputs" AS "outputs" FROM UNNEST(array_agg("outputs" )) AS "_unnest_table"("outputs")     LIMIT $25 ) AS "_sub_query"      ) AS "outputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."output_index")::text AS "output_index", "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."locking_bytecode" AS "locking_bytecode"       ) AS "_e"      ) ) AS "outputs" FROM  (SELECT  *  FROM "public"."output"  WHERE ((("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash") = ("transaction_hash")) AND (("public"."output_locking_bytecode_pattern"("public"."output".*) ) LIKE (($3)::text)))     ) AS "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs" ON ($26) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."inputs" ), $27 )  FROM  (SELECT  "_unnest_table"."inputs" AS "inputs" FROM UNNEST(array_agg("inputs" )) AS "_unnest_table"("inputs")     LIMIT $28 ) AS "_sub_query"      ) AS "inputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"."outpoint_index")::text AS "outpoint_index"       ) AS "_e"      ) ) AS "inputs" FROM  (SELECT  *  FROM "public"."input"  WHERE ((("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."internal_id") = ("transaction_internal_id")) AND (("public"."input"."outpoint_index") = (($2)::bigint)))     ) AS "md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs" ON ($29)      ) AS "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction"      ) AS "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction" ON ($30)    ORDER BY "md5_3e9df6841b24f1f40a5034f7fc1c5d8d_root.ar.root.authchains.ar.authchains.migrations.pg.migration_index" DESC NULLS FIRST  ) AS "_root.ar.root.authchains.ar.authchains.migrations"      ) AS "_root.ar.root.authchains.ar.authchains.migrations" ON ($31)      ) AS "_root.ar.root.authchains"      ) AS "_root.ar.root.authchains" ON ($32)      ) AS "_root"
437 min 1% 22 ms 1,196,981 hasura
SELECT (block_output_value_satoshis(block) - block_input_value_satoshis(block))::bigint FROM block WHERE internal_id = block_row.internal_id
308 min 0.9% 10,127 ms 1,827 chaingraph
WITH transactions_in_block (hash, transaction_index) AS (
  VALUES ($1::bytea, $2::bigint),($3::bytea, $4::bigint)
),
accepting_nodes (node_internal_id, accepted_at) AS (
  VALUES ($5, $6::timestamp)
),
joined_transactions (internal_id, transaction_index) AS (
  SELECT db.internal_id, val.transaction_index
    FROM transaction db INNER JOIN transactions_in_block val ON val.hash = db.hash
),
inserted_block (internal_id) AS (
  INSERT INTO block (height, version, timestamp, hash, previous_block_hash, merkle_root, bits, nonce, size_bytes)
    VALUES ($7, $8, $9,
      $10::bytea,
      $11::bytea,
      $12::bytea,
      $13::bigint, $14::bigint, $15::bigint)
  ON CONFLICT ON CONSTRAINT "block_hash_key" DO NOTHING
  RETURNING internal_id
),
inserted_block_transactions AS (
  INSERT INTO block_transaction (block_internal_id, transaction_internal_id, transaction_index)
    SELECT blk.internal_id, tx.internal_id, tx.transaction_index
      FROM inserted_block blk CROSS JOIN joined_transactions tx
),
new_or_existing_block (internal_id) AS (
  SELECT COALESCE (
    (SELECT internal_id FROM inserted_block),
    (SELECT internal_id FROM block WHERE block.hash = $16::bytea)
  )
)
INSERT INTO node_block (node_internal_id, block_internal_id, accepted_at)
  SELECT node.node_internal_id, blk.internal_id, node.accepted_at
    FROM new_or_existing_block blk CROSS JOIN accepting_nodes node
  ON CONFLICT ON CONSTRAINT "node_block_pkey" DO NOTHING
291 min 0.9% 388 ms 45,032 hasura
SELECT  coalesce(json_agg("root" ), $3 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."locking_bytecode" AS "locking_bytecode", "_root.base"."transaction_hash" AS "transaction_hash", ("_root.base"."fungible_token_amount")::text AS "fungible_token_amount"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."output"  WHERE ((("public"."output"."token_category") = (($2)::bytea)) AND (NOT ((EXISTS  (SELECT  $4  FROM "public"."input" AS "__be_0_input" WHERE (((("__be_0_input"."outpoint_transaction_hash") = ("public"."output"."transaction_hash")) AND ((("__be_0_input"."outpoint_index") = ("public"."output"."output_index")) AND ($5))) AND (($6) AND (($7) AND ($8))))     )) AND ($9))))    LIMIT $10 OFFSET $11) AS "_root.base"      ) AS "_root"
196 min 0.6% 291 ms 40,378 hasura
SELECT  coalesce(json_agg("root" ), $3 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."hash" AS "hash", "_root.ar.root.authchains"."authchains" AS "authchains"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("public"."transaction"."hash") = (($2)::bytea))    LIMIT $4 ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."authchains" ), $5 )  FROM  (SELECT  "_unnest_table"."authchains" AS "authchains" FROM UNNEST(array_agg("authchains" )) AS "_unnest_table"("authchains")     LIMIT $6 ) AS "_sub_query"      ) AS "authchains" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.or.authhead"."authhead" AS "authhead"       ) AS "_e"      ) ) AS "authchains" FROM  (SELECT  *  FROM "public"."authchain_view"  WHERE (("_root.base"."internal_id") = ("transaction_internal_id"))     ) AS "_root.ar.root.authchains.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.or.authhead.base"."hash" AS "hash"       ) AS "_e"      ) ) AS "authhead" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("_root.ar.root.authchains.base"."authhead_transaction_hash") = ("hash"))    LIMIT $7 ) AS "_root.ar.root.authchains.or.authhead.base"      ) AS "_root.ar.root.authchains.or.authhead" ON ($8)      ) AS "_root.ar.root.authchains"      ) AS "_root.ar.root.authchains" ON ($9)      ) AS "_root"
181 min 0.5% 29,048 ms 373 chaingraph
WITH transactions_in_block (hash, transaction_index) AS (
  VALUES ($1::bytea, $2::bigint),($3::bytea, $4::bigint),($5::bytea, $6::bigint)
),
accepting_nodes (node_internal_id, accepted_at) AS (
  VALUES ($7, $8::timestamp)
),
joined_transactions (internal_id, transaction_index) AS (
  SELECT db.internal_id, val.transaction_index
    FROM transaction db INNER JOIN transactions_in_block val ON val.hash = db.hash
),
inserted_block (internal_id) AS (
  INSERT INTO block (height, version, timestamp, hash, previous_block_hash, merkle_root, bits, nonce, size_bytes)
    VALUES ($9, $10, $11,
      $12::bytea,
      $13::bytea,
      $14::bytea,
      $15::bigint, $16::bigint, $17::bigint)
  ON CONFLICT ON CONSTRAINT "block_hash_key" DO NOTHING
  RETURNING internal_id
),
inserted_block_transactions AS (
  INSERT INTO block_transaction (block_internal_id, transaction_internal_id, transaction_index)
    SELECT blk.internal_id, tx.internal_id, tx.transaction_index
      FROM inserted_block blk CROSS JOIN joined_transactions tx
),
new_or_existing_block (internal_id) AS (
  SELECT COALESCE (
    (SELECT internal_id FROM inserted_block),
    (SELECT internal_id FROM block WHERE block.hash = $18::bytea)
  )
)
INSERT INTO node_block (node_internal_id, block_internal_id, accepted_at)
  SELECT node.node_internal_id, blk.internal_id, node.accepted_at
    FROM new_or_existing_block blk CROSS JOIN accepting_nodes node
  ON CONFLICT ON CONSTRAINT "node_block_pkey" DO NOTHING
138 min 0.4% 41 ms 203,860 hasura
SELECT  "_subs"."result_id" , "_fld_resp"."root" AS "result" FROM UNNEST(($1)::uuid[], ($2)::json[]) AS "_subs"("result_id", "result_vars") LEFT OUTER JOIN LATERAL (SELECT  json_build_object($3, "node"."root" ) AS "root" FROM  (SELECT  coalesce(json_agg("root" ), $4 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."name" AS "name", "_root.base"."user_agent" AS "user_agent", ("public"."node_unconfirmed_transaction_count"("_root.base") )::text AS "unconfirmed_transaction_count", "_root.ar.root.unconfirmed_transactions"."unconfirmed_transactions" AS "unconfirmed_transactions", $5 AS "__typename"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."node"  WHERE (("public"."node"."name") ~ ((("_subs"."result_vars"#>>ARRAY[$6, $7]))::text))    LIMIT $8 ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."unconfirmed_transactions" ORDER BY "ob_col_1" DESC NULLS FIRST), $9 )  FROM  (SELECT  "_unnest_table"."unconfirmed_transactions" AS "unconfirmed_transactions", "_unnest_table"."ob_col_1" AS "ob_col_1" FROM UNNEST(array_agg("unconfirmed_transactions" ), array_agg("root.ar.root.unconfirmed_transactions.pg.validated_at" )) AS "_unnest_table"("unconfirmed_transactions", "ob_col_1")    ORDER BY "ob_col_1" DESC NULLS FIRST LIMIT $10 ) AS "_sub_query"      ) AS "unconfirmed_transactions" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.unconfirmed_transactions.base"."validated_at" AS "validated_at", "_root.ar.root.unconfirmed_transactions.or.transaction"."transaction" AS "transaction", $11 AS "__typename"       ) AS "_e"      ) ) AS "unconfirmed_transactions", "_root.ar.root.unconfirmed_transactions.base"."validated_at" AS "root.ar.root.unconfirmed_transactions.pg.validated_at" FROM  (SELECT  *  FROM "public"."node_transaction"  WHERE (("_root.base"."internal_id") = ("node_internal_id"))   ORDER BY "validated_at" DESC NULLS FIRST LIMIT $12 OFFSET $13) AS "_root.ar.root.unconfirmed_transactions.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.unconfirmed_transactions.or.transaction.base"."hash" AS "hash", ("public"."transaction_input_value_satoshis"("_root.ar.root.unconfirmed_transactions.or.transaction.base") )::text AS "input_value_satoshis", ("public"."transaction_output_value_satoshis"("_root.ar.root.unconfirmed_transactions.or.transaction.base") )::text AS "output_value_satoshis", "_root.ar.root.unconfirmed_transactions.or.transaction.base"."is_coinbase" AS "is_coinbase", "md5_b6cc12c09542ad52ba9b87baa9b812ef__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs"."inputs" AS "inputs", "md5_408769f88a061e776e7caeed495b93f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs"."outputs" AS "outputs", $14 AS "__typename"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("_root.ar.root.unconfirmed_transactions.base"."transaction_internal_id") = ("internal_id"))    LIMIT $15 ) AS "_root.ar.root.unconfirmed_transactions.or.transaction.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."outputs" ), $16 )  FROM  (SELECT  "_unnest_table"."outputs" AS "outputs" FROM UNNEST(array_agg("outputs" )) AS "_unnest_table"("outputs")     LIMIT $17 ) AS "_sub_query"      ) AS "outputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."token_category" AS "token_category", ("md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."fungible_token_amount")::text AS "fungible_token_amount", "md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."nonfungible_token_capability" AS "nonfungible_token_capability", "md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."nonfungible_token_commitment" AS "nonfungible_token_commitment", "md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."locking_bytecode" AS "locking_bytecode", ("md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."value_satoshis")::text AS "value_satoshis", $18 AS "__typename"       ) AS "_e"      ) ) AS "outputs" FROM  (SELECT  *  FROM "public"."output"  WHERE (("_root.ar.root.unconfirmed_transactions.or.transaction.base"."hash") = ("transaction_hash"))     ) AS "md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"      ) AS "md5_408769f88a061e776e7caeed495b93f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs"      ) AS "md5_408769f88a061e776e7caeed495b93f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs" ON ($19) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."inputs" ), $20 )  FROM  (SELECT  "_unnest_table"."inputs" AS "inputs" FROM UNNEST(array_agg("inputs" )) AS "_unnest_table"("inputs")     LIMIT $21 ) AS "_sub_query"      ) AS "inputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_449b6821e778500d7dd8178a97963065__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint"."outpoint" AS "outpoint", $22 AS "__typename"       ) AS "_e"      ) ) AS "inputs" FROM  (SELECT  *  FROM "public"."input"  WHERE (("_root.ar.root.unconfirmed_transactions.or.transaction.base"."internal_id") = ("transaction_internal_id"))     ) AS "md5_e182d29c2b2d2f240b3e67ae68696469__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."token_category" AS "token_category", ("md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."fungible_token_amount")::text AS "fungible_token_amount", "md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."nonfungible_token_capability" AS "nonfungible_token_capability", "md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."nonfungible_token_commitment" AS "nonfungible_token_commitment", "md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."locking_bytecode" AS "locking_bytecode", ("md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."value_satoshis")::text AS "value_satoshis", $23 AS "__typename"       ) AS "_e"      ) ) AS "outpoint" FROM  (SELECT  *  FROM "public"."output"  WHERE ((("md5_e182d29c2b2d2f240b3e67ae68696469__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.base"."outpoint_index") = ("output_index")) AND (("md5_e182d29c2b2d2f240b3e67ae68696469__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.base"."outpoint_transaction_hash") = ("transaction_hash")))    LIMIT $24 ) AS "md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"      ) AS "md5_449b6821e778500d7dd8178a97963065__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint" ON ($25)      ) AS "md5_b6cc12c09542ad52ba9b87baa9b812ef__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs"      ) AS "md5_b6cc12c09542ad52ba9b87baa9b812ef__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs" ON ($26)      ) AS "_root.ar.root.unconfirmed_transactions.or.transaction" ON ($27)    ORDER BY "root.ar.root.unconfirmed_transactions.pg.validated_at" DESC NULLS FIRST  ) AS "_root.ar.root.unconfirmed_transactions"      ) AS "_root.ar.root.unconfirmed_transactions" ON ($28)      ) AS "_root"      ) AS "node"      ) AS "_fld_resp" ON ($29)
76 min 0.2% 26,180 ms 175 chaingraph
WITH transactions_in_block (hash, transaction_index) AS (
  VALUES ($1::bytea, $2::bigint),($3::bytea, $4::bigint),($5::bytea, $6::bigint),($7::bytea, $8::bigint)
),
accepting_nodes (node_internal_id, accepted_at) AS (
  VALUES ($9, $10::timestamp)
),
joined_transactions (internal_id, transaction_index) AS (
  SELECT db.internal_id, val.transaction_index
    FROM transaction db INNER JOIN transactions_in_block val ON val.hash = db.hash
),
inserted_block (internal_id) AS (
  INSERT INTO block (height, version, timestamp, hash, previous_block_hash, merkle_root, bits, nonce, size_bytes)
    VALUES ($11, $12, $13,
      $14::bytea,
      $15::bytea,
      $16::bytea,
      $17::bigint, $18::bigint, $19::bigint)
  ON CONFLICT ON CONSTRAINT "block_hash_key" DO NOTHING
  RETURNING internal_id
),
inserted_block_transactions AS (
  INSERT INTO block_transaction (block_internal_id, transaction_internal_id, transaction_index)
    SELECT blk.internal_id, tx.internal_id, tx.transaction_index
      FROM inserted_block blk CROSS JOIN joined_transactions tx
),
new_or_existing_block (internal_id) AS (
  SELECT COALESCE (
    (SELECT internal_id FROM inserted_block),
    (SELECT internal_id FROM block WHERE block.hash = $20::bytea)
  )
)
INSERT INTO node_block (node_internal_id, block_internal_id, accepted_at)
  SELECT node.node_internal_id, blk.internal_id, node.accepted_at
    FROM new_or_existing_block blk CROSS JOIN accepting_nodes node
  ON CONFLICT ON CONSTRAINT "node_block_pkey" DO NOTHING
40 min 0.1% 607 ms 3,943 hasura
SELECT  coalesce(json_agg("root" ), $4 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."hash" AS "hash", "_root.ar.root.authchains"."authchains" AS "authchains"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("public"."transaction"."hash") = (($3)::bytea))    LIMIT $5 ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."authchains" ), $6 )  FROM  (SELECT  "_unnest_table"."authchains" AS "authchains" FROM UNNEST(array_agg("authchains" )) AS "_unnest_table"("authchains")     LIMIT $7 ) AS "_sub_query"      ) AS "authchains" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.or.authhead"."authhead" AS "authhead", "_root.ar.root.authchains.base"."authchain_length" AS "authchain_length", "_root.ar.root.authchains.ar.authchains.migrations"."migrations" AS "migrations"       ) AS "_e"      ) ) AS "authchains" FROM  (SELECT  *  FROM "public"."authchain_view"  WHERE (("_root.base"."internal_id") = ("transaction_internal_id"))     ) AS "_root.ar.root.authchains.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.or.authhead.base"."hash" AS "hash"       ) AS "_e"      ) ) AS "authhead" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("_root.ar.root.authchains.base"."authhead_transaction_hash") = ("hash"))    LIMIT $8 ) AS "_root.ar.root.authchains.or.authhead.base"      ) AS "_root.ar.root.authchains.or.authhead" ON ($9) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."migrations" ORDER BY "ob_col_1" DESC NULLS FIRST), $10 )  FROM  (SELECT  "_unnest_table"."migrations" AS "migrations", "_unnest_table"."ob_col_1" AS "ob_col_1" FROM UNNEST(array_agg("migrations" ), array_agg("md5_3e9df6841b24f1f40a5034f7fc1c5d8d_root.ar.root.authchains.ar.authchains.migrations.pg.migration_index" )) AS "_unnest_table"("migrations", "ob_col_1")    ORDER BY "ob_col_1" DESC NULLS FIRST LIMIT $11 ) AS "_sub_query"      ) AS "migrations" FROM  (SELECT  "_root.ar.root.authchains.ar.authchains.migrations.base"."migration_index" AS "md5_3e9df6841b24f1f40a5034f7fc1c5d8d_root.ar.root.authchains.ar.authchains.migrations.pg.migration_index", row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction"."transaction" AS "transaction"       ) AS "_e"      ) ) AS "migrations" FROM  (SELECT  *  FROM "public"."authchain_migrations_view"  WHERE (("_root.ar.root.authchains.base"."transaction_internal_id") = ("authbase_internal_id"))   ORDER BY "migration_index" DESC NULLS FIRST  ) AS "_root.ar.root.authchains.ar.authchains.migrations.base" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("transaction" ), $12 ) AS "transaction" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash" AS "hash", "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"."inputs" AS "inputs", "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"."outputs" AS "outputs"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."authchain_migration_transaction"("_root.ar.root.authchains.ar.authchains.migrations.base") AS "_authchain_migration_transaction" WHERE ($13)    LIMIT $14 ) AS "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."outputs" ), $15 )  FROM  (SELECT  "_unnest_table"."outputs" AS "outputs" FROM UNNEST(array_agg("outputs" )) AS "_unnest_table"("outputs")     LIMIT $16 ) AS "_sub_query"      ) AS "outputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."output_index")::text AS "output_index", "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."locking_bytecode" AS "locking_bytecode"       ) AS "_e"      ) ) AS "outputs" FROM  (SELECT  *  FROM "public"."output"  WHERE (("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash") = ("transaction_hash"))     ) AS "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs" ON ($17) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."inputs" ), $18 )  FROM  (SELECT  "_unnest_table"."inputs" AS "inputs" FROM UNNEST(array_agg("inputs" )) AS "_unnest_table"("inputs")     LIMIT $19 ) AS "_sub_query"      ) AS "inputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"."outpoint_index")::text AS "outpoint_index"       ) AS "_e"      ) ) AS "inputs" FROM  (SELECT  *  FROM "public"."input"  WHERE ((("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."internal_id") = ("transaction_internal_id")) AND (("public"."input"."outpoint_index") = (($2)::bigint)))     ) AS "md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs" ON ($20)      ) AS "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction"      ) AS "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction" ON ($21)    ORDER BY "md5_3e9df6841b24f1f40a5034f7fc1c5d8d_root.ar.root.authchains.ar.authchains.migrations.pg.migration_index" DESC NULLS FIRST  ) AS "_root.ar.root.authchains.ar.authchains.migrations"      ) AS "_root.ar.root.authchains.ar.authchains.migrations" ON ($22)      ) AS "_root.ar.root.authchains"      ) AS "_root.ar.root.authchains" ON ($23)      ) AS "_root"
40 min 0.1% 6,042 ms 394 hasura
SELECT  coalesce(json_agg("root" ORDER BY "root.or.block.pg.height" DESC NULLS FIRST), $4 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.or.block"."block" AS "block", "_root.or.transaction"."transaction" AS "transaction", $5 AS "__typename"       ) AS "_e"      ) ) AS "root", "_root.or.block"."root.or.block.pg.height" AS "root.or.block.pg.height" FROM  (SELECT  *  FROM "public"."block_transaction"  WHERE ((EXISTS  (SELECT  $6  FROM "public"."transaction" AS "__be_0_transaction" WHERE (((("__be_0_transaction"."internal_id") = ("public"."block_transaction"."transaction_internal_id")) AND ($7)) AND (($8) AND (((EXISTS  (SELECT  $9  FROM "public"."output" AS "__be_1_output" WHERE (((("__be_1_output"."transaction_hash") = ("__be_0_transaction"."hash")) AND ($10)) AND (($11) AND ((((("__be_1_output"."token_category") = (($2)::bytea)) AND ($12)) AND ($13)) AND ($14))))     )) AND ($15)) AND ($16))))     )) AND (EXISTS  (SELECT  $17  FROM "public"."block" AS "__be_2_block" WHERE (((("__be_2_block"."internal_id") = ("public"."block_transaction"."block_internal_id")) AND ($18)) AND (($19) AND (((EXISTS  (SELECT  $20  FROM "public"."node_block" AS "__be_3_node_block" WHERE (((("__be_3_node_block"."block_internal_id") = ("__be_2_block"."internal_id")) AND ($21)) AND (($22) AND (((EXISTS  (SELECT  $23  FROM "public"."node" AS "__be_4_node" WHERE (((("__be_4_node"."internal_id") = ("__be_3_node_block"."node_internal_id")) AND ($24)) AND (($25) AND ((((("__be_4_node"."name") ~ (($3)::text)) AND ($26)) AND ($27)) AND ($28))))     )) AND ($29)) AND ($30))))     )) AND ($31)) AND ($32))))     )))     ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.or.transaction.base"."hash" AS "hash", ("public"."transaction_input_value_satoshis"("_root.or.transaction.base") )::text AS "input_value_satoshis", ("public"."transaction_output_value_satoshis"("_root.or.transaction.base") )::text AS "output_value_satoshis", $33 AS "__typename"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("_root.base"."transaction_internal_id") = ("internal_id"))    LIMIT $34 ) AS "_root.or.transaction.base"      ) AS "_root.or.transaction" ON ($35) LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("_root.or.block.base"."height")::text AS "height", ("_root.or.block.base"."timestamp")::text AS "timestamp", $36 AS "__typename"       ) AS "_e"      ) ) AS "block", "_root.or.block.base"."height" AS "root.or.block.pg.height" FROM  (SELECT  *  FROM "public"."block"  WHERE (("_root.base"."block_internal_id") = ("internal_id"))    LIMIT $37 ) AS "_root.or.block.base"      ) AS "_root.or.block" ON ($38)    ORDER BY "root.or.block.pg.height" DESC NULLS FIRST LIMIT $39 OFFSET $40) AS "_root"
30 min < 0.1% 942 ms 1,899 hasura
SELECT  coalesce(json_agg("root" ), $8 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."hash" AS "hash", "_root.ar.root.authchains"."authchains" AS "authchains", $9 AS "__typename"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."transaction"  WHERE ((EXISTS  (SELECT  $10  FROM "public"."block_transaction" AS "__be_0_block_transaction" WHERE (((("__be_0_block_transaction"."transaction_internal_id") = ("public"."transaction"."internal_id")) AND ($11)) AND (($12) AND (((EXISTS  (SELECT  $13  FROM "public"."block" AS "__be_1_block" WHERE (((("__be_1_block"."internal_id") = ("__be_0_block_transaction"."block_internal_id")) AND ($14)) AND (($15) AND (((EXISTS  (SELECT  $16  FROM "public"."node_block" AS "__be_2_node_block" WHERE (((("__be_2_node_block"."block_internal_id") = ("__be_1_block"."internal_id")) AND ($17)) AND (($18) AND (((EXISTS  (SELECT  $19  FROM "public"."node" AS "__be_3_node" WHERE (((("__be_3_node"."internal_id") = ("__be_2_node_block"."node_internal_id")) AND ($20)) AND (($21) AND ((((("__be_3_node"."name") ~ (($6)::text)) AND ($22)) AND ($23)) AND ($24))))     )) AND ($25)) AND ($26))))     )) AND ($27)) AND ($28))))     )) AND ($29)) AND ($30))))     )) AND (("public"."transaction"."hash") = ANY(($7)::bytea[])))    LIMIT $31 ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."authchains" ), $32 )  FROM  (SELECT  "_unnest_table"."authchains" AS "authchains" FROM UNNEST(array_agg("authchains" )) AS "_unnest_table"("authchains")     LIMIT $33 ) AS "_sub_query"      ) AS "authchains" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.base"."authchain_length" AS "authchain_length", "_root.ar.root.authchains.or.authhead"."authhead" AS "authhead", "_root.ar.root.authchains.ar.authchains.migrations"."migrations" AS "migrations", $34 AS "__typename"       ) AS "_e"      ) ) AS "authchains" FROM  (SELECT  *  FROM "public"."authchain_view"  WHERE (("_root.base"."internal_id") = ("transaction_internal_id"))     ) AS "_root.ar.root.authchains.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.or.authhead.base"."hash" AS "hash", "_root.ar.root.authchains.or.authhead.cf.identity_output"."identity_output" AS "identity_output", $35 AS "__typename"       ) AS "_e"      ) ) AS "authhead" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("_root.ar.root.authchains.base"."authhead_transaction_hash") = ("hash"))    LIMIT $36 ) AS "_root.ar.root.authchains.or.authhead.base" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("identity_output" ), $37 ) AS "identity_output" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("_root.ar.root.authchains.or.authhead.cf.identity_output.base"."fungible_token_amount")::text AS "fungible_token_amount", $38 AS "__typename"       ) AS "_e"      ) ) AS "identity_output" FROM  (SELECT  *  FROM "public"."transaction_identity_output"("_root.ar.root.authchains.or.authhead.base") AS "_transaction_identity_output" WHERE ($39)    LIMIT $40 ) AS "_root.ar.root.authchains.or.authhead.cf.identity_output.base"      ) AS "_root.ar.root.authchains.or.authhead.cf.identity_output"      ) AS "_root.ar.root.authchains.or.authhead.cf.identity_output" ON ($41)      ) AS "_root.ar.root.authchains.or.authhead" ON ($42) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."migrations" ), $43 )  FROM  (SELECT  "_unnest_table"."migrations" AS "migrations" FROM UNNEST(array_agg("migrations" )) AS "_unnest_table"("migrations")     LIMIT $44 ) AS "_sub_query"      ) AS "migrations" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction"."transaction" AS "transaction", $45 AS "__typename"       ) AS "_e"      ) ) AS "migrations" FROM  (SELECT  *  FROM "public"."authchain_migrations_view"  WHERE ((("_root.ar.root.authchains.base"."transaction_internal_id") = ("authbase_internal_id")) AND ((EXISTS  (SELECT  $46  FROM "public"."authchain_migration_transaction"("public"."authchain_migrations_view".*) AS "__be_0_authchain_migration_transaction" WHERE ((EXISTS  (SELECT  $47  FROM "public"."output" AS "__be_1_output" WHERE (((("__be_1_output"."transaction_hash") = ("__be_0_authchain_migration_transaction"."hash")) AND ($48)) AND (($49) AND ((((("public"."output_locking_bytecode_pattern"("__be_1_output".*) ) ~ (($3)::text)) AND ($50)) AND ($51)) AND ($52))))     )) AND ($53))     )) OR (EXISTS  (SELECT  $54  FROM "public"."authchain_migration_transaction"("public"."authchain_migrations_view".*) AS "__be_2_authchain_migration_transaction" WHERE ((EXISTS  (SELECT  $55  FROM "public"."input" AS "__be_3_input" WHERE (((("__be_3_input"."transaction_internal_id") = ("__be_2_authchain_migration_transaction"."internal_id")) AND ($56)) AND (($57) AND ((((("__be_3_input"."outpoint_index") = (($4)::bigint)) AND ($58)) AND (((("__be_3_input"."outpoint_transaction_hash") = ANY(($5)::bytea[])) AND ($59)) AND ($60))) AND ($61))))     )) AND ($62))     ))))     ) AS "_root.ar.root.authchains.ar.authchains.migrations.base" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("transaction" ), $63 ) AS "transaction" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash" AS "hash", "md5_cf189b29ac9ea2ab2029e3a8f9405135__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.block_inclusions"."block_inclusions" AS "block_inclusions", "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"."inputs" AS "inputs", "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"."outputs" AS "outputs", $64 AS "__typename"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."authchain_migration_transaction"("_root.ar.root.authchains.ar.authchains.migrations.base") AS "_authchain_migration_transaction" WHERE ($65)    LIMIT $66 ) AS "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."outputs" ), $67 )  FROM  (SELECT  "_unnest_table"."outputs" AS "outputs" FROM UNNEST(array_agg("outputs" )) AS "_unnest_table"("outputs")     LIMIT $68 ) AS "_sub_query"      ) AS "outputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."token_category" AS "token_category", "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."locking_bytecode" AS "locking_bytecode", ("md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."fungible_token_amount")::text AS "fungible_token_amount", "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."nonfungible_token_capability" AS "nonfungible_token_capability", "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."nonfungible_token_commitment" AS "nonfungible_token_commitment", $69 AS "__typename"       ) AS "_e"      ) ) AS "outputs" FROM  (SELECT  *  FROM "public"."output"  WHERE (("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash") = ("transaction_hash"))     ) AS "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs" ON ($70) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."inputs" ), $71 )  FROM  (SELECT  "_unnest_table"."inputs" AS "inputs" FROM UNNEST(array_agg("inputs" )) AS "_unnest_table"("inputs")     LIMIT $72 ) AS "_sub_query"      ) AS "inputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"."outpoint_transaction_hash" AS "outpoint_transaction_hash", $73 AS "__typename"       ) AS "_e"      ) ) AS "inputs" FROM  (SELECT  *  FROM "public"."input"  WHERE ((("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."internal_id") = ("transaction_internal_id")) AND (("public"."input"."outpoint_index") = (($2)::bigint)))     ) AS "md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs" ON ($74) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."block_inclusions" ), $75 )  FROM  (SELECT  "_unnest_table"."block_inclusions" AS "block_inclusions" FROM UNNEST(array_agg("block_inclusions" )) AS "_unnest_table"("block_inclusions")     LIMIT $76 ) AS "_sub_query"      ) AS "block_inclusions" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_9b01dfee23a4138dfeb058d133586a4b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.block_in
26 min < 0.1% 377 ms 4,128 hasura
SELECT  coalesce(json_agg("root" ), $3 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains"."authchains" AS "authchains"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("public"."transaction"."hash") = (($2)::bytea))    LIMIT $4 ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."authchains" ), $5 )  FROM  (SELECT  "_unnest_table"."authchains" AS "authchains" FROM UNNEST(array_agg("authchains" )) AS "_unnest_table"("authchains")     LIMIT $6 ) AS "_sub_query"      ) AS "authchains" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.or.authhead"."authhead" AS "authhead"       ) AS "_e"      ) ) AS "authchains" FROM  (SELECT  *  FROM "public"."authchain_view"  WHERE (("_root.base"."internal_id") = ("transaction_internal_id"))     ) AS "_root.ar.root.authchains.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.or.authhead.base"."hash" AS "hash", "_root.ar.root.authchains.or.authhead.cf.identity_output"."identity_output" AS "identity_output"       ) AS "_e"      ) ) AS "authhead" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("_root.ar.root.authchains.base"."authhead_transaction_hash") = ("hash"))    LIMIT $7 ) AS "_root.ar.root.authchains.or.authhead.base" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("identity_output" ), $8 ) AS "identity_output" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("_root.ar.root.authchains.or.authhead.cf.identity_output.base"."fungible_token_amount")::text AS "fungible_token_amount"       ) AS "_e"      ) ) AS "identity_output" FROM  (SELECT  *  FROM "public"."transaction_identity_output"("_root.ar.root.authchains.or.authhead.base") AS "_transaction_identity_output" WHERE ($9)    LIMIT $10 ) AS "_root.ar.root.authchains.or.authhead.cf.identity_output.base"      ) AS "_root.ar.root.authchains.or.authhead.cf.identity_output"      ) AS "_root.ar.root.authchains.or.authhead.cf.identity_output" ON ($11)      ) AS "_root.ar.root.authchains.or.authhead" ON ($12)      ) AS "_root.ar.root.authchains"      ) AS "_root.ar.root.authchains" ON ($13)      ) AS "_root"
24 min < 0.1% 788 ms 1,815 hasura
SELECT  coalesce(json_agg("root" ), $6 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."hash" AS "hash", "_root.ar.root.authchains"."authchains" AS "authchains"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("public"."transaction"."hash") = (($5)::bytea))    LIMIT $7 ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."authchains" ), $8 )  FROM  (SELECT  "_unnest_table"."authchains" AS "authchains" FROM UNNEST(array_agg("authchains" )) AS "_unnest_table"("authchains")     LIMIT $9 ) AS "_sub_query"      ) AS "authchains" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.base"."authchain_length" AS "authchain_length", "_root.ar.root.authchains.ar.authchains.migrations"."migrations" AS "migrations"       ) AS "_e"      ) ) AS "authchains" FROM  (SELECT  *  FROM "public"."authchain_view"  WHERE (("_root.base"."internal_id") = ("transaction_internal_id"))     ) AS "_root.ar.root.authchains.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."migrations" ), $10 )  FROM  (SELECT  "_unnest_table"."migrations" AS "migrations" FROM UNNEST(array_agg("migrations" )) AS "_unnest_table"("migrations")     LIMIT $11 ) AS "_sub_query"      ) AS "migrations" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction"."transaction" AS "transaction"       ) AS "_e"      ) ) AS "migrations" FROM  (SELECT  *  FROM "public"."authchain_migrations_view"  WHERE ((("_root.ar.root.authchains.base"."transaction_internal_id") = ("authbase_internal_id")) AND (EXISTS  (SELECT  $12  FROM "public"."authchain_migration_transaction"("public"."authchain_migrations_view".*) AS "__be_0_authchain_migration_transaction" WHERE ((EXISTS  (SELECT  $13  FROM "public"."output" AS "__be_1_output" WHERE (((("__be_1_output"."transaction_hash") = ("__be_0_authchain_migration_transaction"."hash")) AND ($14)) AND (($15) AND ((((("public"."output_locking_bytecode_pattern"("__be_1_output".*) ) LIKE (($4)::text)) AND ($16)) AND ($17)) AND ($18))))     )) AND ($19))     )))     ) AS "_root.ar.root.authchains.ar.authchains.migrations.base" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("transaction" ), $20 ) AS "transaction" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash" AS "hash", "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"."inputs" AS "inputs", "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"."outputs" AS "outputs"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."authchain_migration_transaction"("_root.ar.root.authchains.ar.authchains.migrations.base") AS "_authchain_migration_transaction" WHERE ($21)    LIMIT $22 ) AS "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."outputs" ), $23 )  FROM  (SELECT  "_unnest_table"."outputs" AS "outputs" FROM UNNEST(array_agg("outputs" )) AS "_unnest_table"("outputs")     LIMIT $24 ) AS "_sub_query"      ) AS "outputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."output_index")::text AS "output_index", "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."locking_bytecode" AS "locking_bytecode"       ) AS "_e"      ) ) AS "outputs" FROM  (SELECT  *  FROM "public"."output"  WHERE ((("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash") = ("transaction_hash")) AND (("public"."output_locking_bytecode_pattern"("public"."output".*) ) LIKE (($3)::text)))     ) AS "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs" ON ($25) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."inputs" ), $26 )  FROM  (SELECT  "_unnest_table"."inputs" AS "inputs" FROM UNNEST(array_agg("inputs" )) AS "_unnest_table"("inputs")     LIMIT $27 ) AS "_sub_query"      ) AS "inputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"."outpoint_index")::text AS "outpoint_index"       ) AS "_e"      ) ) AS "inputs" FROM  (SELECT  *  FROM "public"."input"  WHERE ((("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."internal_id") = ("transaction_internal_id")) AND (("public"."input"."outpoint_index") = (($2)::bigint)))     ) AS "md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs" ON ($28)      ) AS "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction"      ) AS "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction" ON ($29)      ) AS "_root.ar.root.authchains.ar.authchains.migrations"      ) AS "_root.ar.root.authchains.ar.authchains.migrations" ON ($30)      ) AS "_root.ar.root.authchains"      ) AS "_root.ar.root.authchains" ON ($31)      ) AS "_root"
18 min < 0.1% 7,680 ms 143 hasura
SELECT  coalesce(json_agg("root" ), $7 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.base"."hash" AS "hash", "_root.ar.root.authchains"."authchains" AS "authchains"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."transaction"  WHERE ((("public"."transaction"."hash") = (($5)::bytea)) AND (EXISTS  (SELECT  $8  FROM "public"."node_transaction_history" AS "__be_0_node_transaction_history" WHERE (((("__be_0_node_transaction_history"."transaction_internal_id") = ("public"."transaction"."internal_id")) AND ($9)) AND (($10) AND (((EXISTS  (SELECT  $11  FROM "public"."node" AS "__be_1_node" WHERE (((("__be_1_node"."internal_id") = ("__be_0_node_transaction_history"."node_internal_id")) AND ($12)) AND (($13) AND ((((("__be_1_node"."name") ILIKE (($6)::text)) AND ($14)) AND ($15)) AND ($16))))     )) AND ($17)) AND ($18))))     )))    LIMIT $19 ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."authchains" ), $20 )  FROM  (SELECT  "_unnest_table"."authchains" AS "authchains" FROM UNNEST(array_agg("authchains" )) AS "_unnest_table"("authchains")     LIMIT $21 ) AS "_sub_query"      ) AS "authchains" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.authchains.base"."authchain_length" AS "authchain_length", "_root.ar.root.authchains.ar.authchains.migrations"."migrations" AS "migrations"       ) AS "_e"      ) ) AS "authchains" FROM  (SELECT  *  FROM "public"."authchain_view"  WHERE (("_root.base"."internal_id") = ("transaction_internal_id"))     ) AS "_root.ar.root.authchains.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."migrations" ), $22 )  FROM  (SELECT  "_unnest_table"."migrations" AS "migrations" FROM UNNEST(array_agg("migrations" )) AS "_unnest_table"("migrations")     LIMIT $23 ) AS "_sub_query"      ) AS "migrations" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction"."transaction" AS "transaction"       ) AS "_e"      ) ) AS "migrations" FROM  (SELECT  *  FROM "public"."authchain_migrations_view"  WHERE ((("_root.ar.root.authchains.base"."transaction_internal_id") = ("authbase_internal_id")) AND (EXISTS  (SELECT  $24  FROM "public"."authchain_migration_transaction"("public"."authchain_migrations_view".*) AS "__be_0_authchain_migration_transaction" WHERE ((EXISTS  (SELECT  $25  FROM "public"."output" AS "__be_1_output" WHERE (((("__be_1_output"."transaction_hash") = ("__be_0_authchain_migration_transaction"."hash")) AND ($26)) AND (($27) AND ((((("public"."output_locking_bytecode_pattern"("__be_1_output".*) ) LIKE (($4)::text)) AND ($28)) AND ($29)) AND ($30))))     )) AND ($31))     )))     ) AS "_root.ar.root.authchains.ar.authchains.migrations.base" LEFT OUTER JOIN LATERAL (SELECT  coalesce(json_agg("transaction" ), $32 ) AS "transaction" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash" AS "hash", "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"."inputs" AS "inputs", "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"."outputs" AS "outputs"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."authchain_migration_transaction"("_root.ar.root.authchains.ar.authchains.migrations.base") AS "_authchain_migration_transaction" WHERE ($33)    LIMIT $34 ) AS "md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."outputs" ), $35 )  FROM  (SELECT  "_unnest_table"."outputs" AS "outputs" FROM UNNEST(array_agg("outputs" )) AS "_unnest_table"("outputs")     LIMIT $36 ) AS "_sub_query"      ) AS "outputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."output_index")::text AS "output_index", "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"."locking_bytecode" AS "locking_bytecode"       ) AS "_e"      ) ) AS "outputs" FROM  (SELECT  *  FROM "public"."output"  WHERE ((("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."hash") = ("transaction_hash")) AND (("public"."output_locking_bytecode_pattern"("public"."output".*) ) LIKE (($3)::text)))     ) AS "md5_422ccf887600486a3c5ada21267a7754__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs.base"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs"      ) AS "md5_b3073ff054c345a9d0e8a6c6cec47958__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.outputs" ON ($37) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."inputs" ), $38 )  FROM  (SELECT  "_unnest_table"."inputs" AS "inputs" FROM UNNEST(array_agg("inputs" )) AS "_unnest_table"("inputs")     LIMIT $39 ) AS "_sub_query"      ) AS "inputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  ("md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"."outpoint_index")::text AS "outpoint_index"       ) AS "_e"      ) ) AS "inputs" FROM  (SELECT  *  FROM "public"."input"  WHERE ((("md5_18c154247a64833a35eed3734150d99b__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.base"."internal_id") = ("transaction_internal_id")) AND (("public"."input"."outpoint_index") = (($2)::bigint)))     ) AS "md5_c57299b49eed7577ccc55c54ffaf4e43__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs.base"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs"      ) AS "md5_3801b81ffaaf85feedad8ee273a3ee8a__root.ar.root.authchains.ar.authchains.migrations.cf.transaction.ar.transaction.inputs" ON ($40)      ) AS "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction"      ) AS "md5_503a3bf0689befcaab27bd8f8f887488__root.ar.root.authchains.ar.authchains.migrations.cf.transaction" ON ($41)      ) AS "_root.ar.root.authchains.ar.authchains.migrations"      ) AS "_root.ar.root.authchains.ar.authchains.migrations" ON ($42)      ) AS "_root.ar.root.authchains"      ) AS "_root.ar.root.authchains" ON ($43)      ) AS "_root"
18 min < 0.1% 3,815 ms 277 hasura
SELECT  coalesce(json_agg("root" ), $4 ) AS "root" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.accepted_blocks"."accepted_blocks" AS "accepted_blocks", "_root.ar.root.unconfirmed_transactions"."unconfirmed_transactions" AS "unconfirmed_transactions", $5 AS "__typename"       ) AS "_e"      ) ) AS "root" FROM  (SELECT  *  FROM "public"."node"  WHERE (("public"."node"."name") ~ (($3)::text))    LIMIT $6 ) AS "_root.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."accepted_blocks" ORDER BY "ob_col_1" DESC NULLS FIRST), $7 )  FROM  (SELECT  "_unnest_table"."accepted_blocks" AS "accepted_blocks", "_unnest_table"."ob_col_1" AS "ob_col_1" FROM UNNEST(array_agg("accepted_blocks" ), array_agg("root.ar.root.accepted_blocks.or.block.pg.height" )) AS "_unnest_table"("accepted_blocks", "ob_col_1")    ORDER BY "ob_col_1" DESC NULLS FIRST LIMIT $8 ) AS "_sub_query"      ) AS "accepted_blocks" FROM  (SELECT  "_root.ar.root.accepted_blocks.or.block"."root.ar.root.accepted_blocks.or.block.pg.height" AS "root.ar.root.accepted_blocks.or.block.pg.height", row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.accepted_blocks.or.block"."block" AS "block", $9 AS "__typename"       ) AS "_e"      ) ) AS "accepted_blocks" FROM  (SELECT  *  FROM "public"."node_block"  WHERE (("_root.base"."internal_id") = ("node_internal_id"))     ) AS "_root.ar.root.accepted_blocks.base" LEFT OUTER JOIN LATERAL (SELECT  "_root.ar.root.accepted_blocks.or.block.base"."height" AS "root.ar.root.accepted_blocks.or.block.pg.height", row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.accepted_blocks.or.block.base"."hash" AS "hash", ("_root.ar.root.accepted_blocks.or.block.base"."timestamp")::text AS "timestamp", ("_root.ar.root.accepted_blocks.or.block.base"."height")::text AS "height", $10 AS "__typename"       ) AS "_e"      ) ) AS "block" FROM  (SELECT  *  FROM "public"."block"  WHERE (("_root.ar.root.accepted_blocks.base"."block_internal_id") = ("internal_id"))    LIMIT $11 ) AS "_root.ar.root.accepted_blocks.or.block.base"      ) AS "_root.ar.root.accepted_blocks.or.block" ON ($12)    ORDER BY "root.ar.root.accepted_blocks.or.block.pg.height" DESC NULLS FIRST LIMIT $13 ) AS "_root.ar.root.accepted_blocks"      ) AS "_root.ar.root.accepted_blocks" ON ($14) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."unconfirmed_transactions" ORDER BY "ob_col_1" DESC NULLS FIRST), $15 )  FROM  (SELECT  "_unnest_table"."unconfirmed_transactions" AS "unconfirmed_transactions", "_unnest_table"."ob_col_1" AS "ob_col_1" FROM UNNEST(array_agg("unconfirmed_transactions" ), array_agg("root.ar.root.unconfirmed_transactions.pg.validated_at" )) AS "_unnest_table"("unconfirmed_transactions", "ob_col_1")    ORDER BY "ob_col_1" DESC NULLS FIRST LIMIT $16 ) AS "_sub_query"      ) AS "unconfirmed_transactions" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.unconfirmed_transactions.or.transaction"."transaction" AS "transaction", $17 AS "__typename"       ) AS "_e"      ) ) AS "unconfirmed_transactions", "_root.ar.root.unconfirmed_transactions.base"."validated_at" AS "root.ar.root.unconfirmed_transactions.pg.validated_at" FROM  (SELECT  *  FROM "public"."node_transaction"  WHERE ((("_root.base"."internal_id") = ("node_internal_id")) AND (EXISTS  (SELECT  $18  FROM "public"."transaction" AS "__be_0_transaction" WHERE (((("__be_0_transaction"."internal_id") = ("public"."node_transaction"."transaction_internal_id")) AND ($19)) AND (($20) AND ((((("__be_0_transaction"."hash") = (($2)::bytea)) AND ($21)) AND ($22)) AND ($23))))     )))   ORDER BY "validated_at" DESC NULLS FIRST  ) AS "_root.ar.root.unconfirmed_transactions.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "_root.ar.root.unconfirmed_transactions.or.transaction.base"."hash" AS "hash", ("public"."transaction_input_value_satoshis"("_root.ar.root.unconfirmed_transactions.or.transaction.base") )::text AS "input_value_satoshis", ("public"."transaction_output_value_satoshis"("_root.ar.root.unconfirmed_transactions.or.transaction.base") )::text AS "output_value_satoshis", "_root.ar.root.unconfirmed_transactions.or.transaction.base"."is_coinbase" AS "is_coinbase", "md5_49487847c71397880ad861be633472aa__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.block_inclusions"."block_inclusions" AS "block_inclusions", "md5_b6cc12c09542ad52ba9b87baa9b812ef__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs"."inputs" AS "inputs", "md5_408769f88a061e776e7caeed495b93f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs"."outputs" AS "outputs", $24 AS "__typename"       ) AS "_e"      ) ) AS "transaction" FROM  (SELECT  *  FROM "public"."transaction"  WHERE (("_root.ar.root.unconfirmed_transactions.base"."transaction_internal_id") = ("internal_id"))    LIMIT $25 ) AS "_root.ar.root.unconfirmed_transactions.or.transaction.base" LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."outputs" ), $26 )  FROM  (SELECT  "_unnest_table"."outputs" AS "outputs" FROM UNNEST(array_agg("outputs" )) AS "_unnest_table"("outputs")     LIMIT $27 ) AS "_sub_query"      ) AS "outputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."token_category" AS "token_category", ("md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."fungible_token_amount")::text AS "fungible_token_amount", "md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."nonfungible_token_commitment" AS "nonfungible_token_commitment", "md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."nonfungible_token_capability" AS "nonfungible_token_capability", "md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."locking_bytecode" AS "locking_bytecode", ("md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"."value_satoshis")::text AS "value_satoshis", $28 AS "__typename"       ) AS "_e"      ) ) AS "outputs" FROM  (SELECT  *  FROM "public"."output"  WHERE (("_root.ar.root.unconfirmed_transactions.or.transaction.base"."hash") = ("transaction_hash"))     ) AS "md5_892124844302a7d0ff4668f281debdc2__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs.base"      ) AS "md5_408769f88a061e776e7caeed495b93f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs"      ) AS "md5_408769f88a061e776e7caeed495b93f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.outputs" ON ($29) LEFT OUTER JOIN LATERAL (SELECT  (SELECT  coalesce(json_agg("_sub_query"."inputs" ), $30 )  FROM  (SELECT  "_unnest_table"."inputs" AS "inputs" FROM UNNEST(array_agg("inputs" )) AS "_unnest_table"("inputs")     LIMIT $31 ) AS "_sub_query"      ) AS "inputs" FROM  (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_e182d29c2b2d2f240b3e67ae68696469__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.base"."unlocking_bytecode" AS "unlocking_bytecode", ("public"."input_value_satoshis"("md5_e182d29c2b2d2f240b3e67ae68696469__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.base") )::text AS "value_satoshis", "md5_449b6821e778500d7dd8178a97963065__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint"."outpoint" AS "outpoint", $32 AS "__typename"       ) AS "_e"      ) ) AS "inputs" FROM  (SELECT  *  FROM "public"."input"  WHERE (("_root.ar.root.unconfirmed_transactions.or.transaction.base"."internal_id") = ("transaction_internal_id"))     ) AS "md5_e182d29c2b2d2f240b3e67ae68696469__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.base" LEFT OUTER JOIN LATERAL (SELECT  row_to_json((SELECT  "_e"  FROM  (SELECT  "md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."token_category" AS "token_category", ("md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."fungible_token_amount")::text AS "fungible_token_amount", "md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."nonfungible_token_commitment" AS "nonfungible_token_commitment", "md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."nonfungible_token_capability" AS "nonfungible_token_capability", "md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."locking_bytecode" AS "locking_bytecode", ("md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"."value_satoshis")::text AS "value_satoshis", $33 AS "__typename"       ) AS "_e"      ) ) AS "outpoint" FROM  (SELECT  *  FROM "public"."output"  WHERE ((("md5_e182d29c2b2d2f240b3e67ae68696469__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.base"."outpoint_index") = ("output_index")) AND (("md5_e182d29c2b2d2f240b3e67ae68696469__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.base"."outpoint_transaction_hash") = ("transaction_hash")))    LIMIT $34 ) AS "md5_8590517439f1540cd51f86ae22eb97f8__root.ar.root.unconfirmed_transactions.or.transaction.ar.transaction.inputs.or.outpoint.base"      ) AS "md5_449b6821e778500d7dd8178a97963065__root.ar.root.unconfirmed_transactions.or.transaction.a
15 min < 0.1% 1,759 ms 526 chaingraph
WITH query_stats AS ( SELECT LEFT(query, $1) AS query, queryid AS query_hash, rolname AS user, ((total_plan_time + total_exec_time) / $2 / $3) AS total_minutes, ((total_plan_time + total_exec_time) / calls) AS average_time, calls FROM pg_stat_statements INNER JOIN pg_database ON pg_database.oid = pg_stat_statements.dbid INNER JOIN pg_roles ON pg_roles.oid = pg_stat_statements.userid WHERE calls > $4 AND pg_database.datname = current_database() ) SELECT query, query AS explainable_query, query_hash, query_stats.user, total_minutes, average_time, calls, total_minutes * $5 / (SELECT SUM(total_minutes) FROM query_stats) AS total_percent, (SELECT SUM(total_minutes) FROM query_stats) AS all_queries_total_minutes FROM query_stats ORDER BY "total_minutes" DESC LIMIT $6 /*pghero*/