Bad performance on recursive query in PostgreSQL

You're having to sort, because you don't have an index on parent_id..

"comment_pkey" PRIMARY KEY, btree (id)
"index_comment_id_parent_id" UNIQUE, btree (id, parent_id)

This is also redundant. There is no need for a UNIQUE index on on (id, parent_id) when you're already UNIQUE on id.

Solution: Drop the index on id, parent_id, create an index on parent_id.

Moreover, you're having to sort on ct.created_date DESC. This is because your only index is on

(website_page_id, parent_id, deleted_date, created_date DESC)

That's a huge index. It's also not useful here at all.

Solution: Drop this overly compound index, and create one on ct.created_date DESC

Don't forget to vacuum analyze.

Note this query won't ever be fast. You're essentially processing 1000010 rows even if you only need 10. Consider not asking for the hierarchy for the whole database to get 10 rows.