Bad performance on recursive query in PostgreSQL

I have bad performance with recursive query in PosgreSQL.

psql --version
psql (PostgreSQL) 10.7 (Ubuntu 10.7-1.pgdg16.04+1)

The idea is to have tree-like structure of comments. So when parent_id is NULL it is parent comment, when it is integer then it is a reply.

I have the following table structure for comment table (\d+ comment):

```
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-----------------+--------------------------+-----------+----------+-------------------------------------+----------+--------------+-------------
id | bigint | | not null | nextval('comment_id_seq'::regclass) | plain | |
website_page_id | bigint | | not null | | plain | |
author_id | bigint | | not null | | plain | |
parent_id | bigint | | | | plain | |
content | text | | | | extended | |
deleted_date | timestamp with time zone | | | | plain | |
updated_date | timestamp with time zone | | not null | | plain | |
created_date | timestamp with time zone | | not null | | plain | |
Indexes:
"comment_pkey" PRIMARY KEY, btree (id)
"index_comment_id_parent_id" UNIQUE, btree (id, parent_id)
"index_comment_website_page_id_deleted_date" btree (website_page_id, deleted_date)
"index_comment_website_page_id_parent_id_deleted_and_created_dat" btree (webs

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.

StackExchange Database Administrators Q#237922, answer score: 3