Update: see also part two
Activity Monitor manages to do this, but it's not clear what hooks it has into asking Safari for the mapping between URLs and pids. If you enable a debug option to append pids to tab names, you can get at it with AppleScript, like so:
#!/bin/bash
#
# Enable Safari's debug menu with
# defaults write com.apple.Safari IncludeInternalDebugMenu 1
# Enable showing pid in tab titles by choosing
# Debug...Miscellaneous Flags...Show Web Process IDs in Page Titles
#
# With that configured, this script will show tabs that ordered by
# memory consumption, descending.
#
# Combine with fzf to facilitate killing the tabs:
# kill $(./safari-top.sh | fzf -m --no-sort | awk '{ print $1 }')
join <(
echo 'tell application "Safari"
set windowCount to count of windows
repeat with windowIndex from 1 to windowCount
set tabCount to count of tabs of window windowIndex
repeat with tabIndex from 1 to tabCount
set currentTab to tab tabIndex of window windowIndex
set tabName to name of currentTab
set tabURL to URL of currentTab
log tabName & "\t" & tabURL
end repeat
end repeat
end tell
' | osascript 2> /dev/stdout | perl -pe 's,^.* ([0-9]+)\](.*)$,$1 $2,' | sort) <(
ps -e -opid=,rss= | awk 'BEGIN { OFS="\t" } { print $1, int($2/1024.0) }' | sort) | sort -n -r -k 3