Tables involved are mastodon_notification and mastodon_relationship.
Originally the plugin provided only the JSONB column mastodon_notification.account. So the join I was looking for was from a field of that JSONB column, mastodon_notification.account'->>'id', to a corresponding native column mastodon_relationship.id.
This is the query plan.
> explain select r.following from mastodon_notification n join mastodon_relationship r on n.account ->> 'id' = r.id limit 1
+---------------------------------------------------------------------------------------------------->
| QUERY PLAN >
+---------------------------------------------------------------------------------------------------->
| Limit (cost=0.00..2000.08 rows=1 width=1) >
| -> Nested Loop (cost=0.00..10000400020000.00 rows=5000000000 width=1) >
| -> Foreign Scan on mastodon_notification n (cost=0.00..10000000000000.00 rows=1000000 wid>
| -> Foreign Scan on mastodon_relationship r (cost=0.00..400.00 rows=2 width=200) >
| Filter: ((n.account ->> 'id'::text) = id) >
+----------------------------------------------------------------------------------------------------
But it doesn't work.
select r.following from mastodon_notification n join mastodon_relationship r on r.id = n.account ->> 'id' limit 1
Error: rpc error: code = Internal desc = 'List' call for table 'mastodon_relationship' is missing 1 required qual: column:'id' operator: =
If the value is constant it does work.
> explain select r.following from mastodon_notification n join mastodon_relationship r on r.id = '267944' limit 1
+---------------------------------------------------------------------------------------------------->
| QUERY PLAN >
+---------------------------------------------------------------------------------------------------->
| Limit (cost=0.00..20.01 rows=1 width=1) >
| -> Nested Loop (cost=0.00..20010000000000.00 rows=1000000000000 width=1) >
| -> Foreign Scan on mastodon_relationship r (cost=0.00..20000000000000.00 rows=1000000 wid>
| Filter: (id = '267944'::text) >
| -> Foreign Scan on mastodon_notification n (cost=0.00..0.00 rows=1000000 width=0) >
+----------------------------------------------------------------------------------------------------
> select r.following from mastodon_notification n join mastodon_relationship r on r.id = '267944' limit 1
+-----------+
| following |
+-----------+
| false |
+-----------+
Given that a constant value works, I wondered if hoisting the id from JSONB into a native column would work.
Plugin changes:
{
Name: "account_id",
Type: proto.ColumnType_STRING,
Description: "Account ID of notification sender.",
Transform: transform.FromValue().Transform(notification_account_id),
},
func notification_account_id(ctx context.Context, input *transform.TransformData) (interface{}, error) {
notification := input.Value.(*mastodon.Notification)
return notification.Account.ID, nil
}
That works!
> explain select r.following from mastodon_notification n join mastodon_relationship r on r.id = n.account_id limit 1
+---------------------------------------------------------------------------------------------------->
| QUERY PLAN >
+---------------------------------------------------------------------------------------------------->
| Limit (cost=0.00..2000.08 rows=1 width=1) >
| -> Nested Loop (cost=0.00..10000400020000.00 rows=5000000000 width=1) >
| -> Foreign Scan on mastodon_notification n (cost=0.00..10000000000000.00 rows=1000000 wid>
| -> Foreign Scan on mastodon_relationship r (cost=0.00..400.00 rows=2 width=200) >
| Filter: (id = n.account_id) >
+---------------------------------------------------------------------------------------------------->
> select r.following from mastodon_notification n join mastodon_relationship r on r.id = n.account_id limit 1
+-----------+
| following |
+-----------+
| false |
+-----------+