3

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 (website_page_id, parent_id, deleted_date, created_date DESC)
Foreign-key constraints:
    "fk_comment_author_id_id_author" FOREIGN KEY (author_id) REFERENCES author(id) ON DELETE CASCADE
    "fk_comment_parent_id_comment_id" FOREIGN KEY (parent_id) REFERENCES comment(id) ON DELETE CASCADE
    "fk_comment_website_page_id_website_page" FOREIGN KEY (website_page_id) REFERENCES website_page(id) ON DELETE CASCADE
Referenced by:
    TABLE "comment" CONSTRAINT "fk_comment_parent_id_comment_id" FOREIGN KEY (parent_id) REFERENCES comment(id) ON DELETE CASCADE

Here is EXPLAIN ANALYZE query:

EXPLAIN ANALYZE WITH RECURSIVE ct (id, author_id, parent_id, content, depth, updated_date, created_date)  AS ( 
    SELECT  
        id, 
        author_id, 
        parent_id, 
        content, 
        0 AS depth, 
        updated_date, 
        created_date
    FROM    comment c1
    WHERE   website_page_id = 1 AND parent_id IS NULL AND deleted_date IS NULL
    UNION ALL
    SELECT
        comment.id, 
        comment.author_id, 
        comment.parent_id, 
        comment.content, 
        depth + 1 AS depth,
        comment.updated_date,
        comment.created_date
    FROM    comment
    JOIN ct ON comment.parent_id = ct.id
)
SELECT * FROM ct ORDER BY created_date DESC LIMIT 10;

Here is the result of query plan:

Limit  (cost=21024979.19..21024979.22 rows=10 width=76) (actual time=2951.338..2951.341 rows=10 loops=1)
   CTE ct
     ->  Recursive Union  (cost=0.00..20290183.86 rows=17659257 width=76) (actual time=0.031..2547.332 rows=1000010 loops=1)
           ->  Seq Scan on comment c1  (cost=0.00..25834.12 rows=999977 width=76) (actual time=0.027..175.619 rows=1000004 loops=1)
                 Filter: ((parent_id IS NULL) AND (deleted_date IS NULL) AND (website_page_id = 1))
                 Rows Removed by Filter: 6
           ->  Merge Join  (cost=1909463.84..1991116.46 rows=1665928 width=76) (actual time=499.348..499.352 rows=2 loops=4)
                 Merge Cond: (ct_1.id = comment.parent_id)
                 ->  Sort  (cost=1704437.83..1729437.26 rows=9999770 width=12) (actual time=126.066..126.067 rows=3 loops=4)
                       Sort Key: ct_1.id
                       Sort Method: quicksort  Memory: 25kB
                       ->  WorkTable Scan on ct ct_1  (cost=0.00..199995.40 rows=9999770 width=12) (actual time=0.005..32.293 rows=250002 loops=4)
                 ->  Materialize  (cost=205026.01..210026.06 rows=1000010 width=72) (actual time=370.859..370.860 rows=6 loops=4)
                       ->  Sort  (cost=205026.01..207526.04 rows=1000010 width=72) (actual time=370.856..370.857 rows=6 loops=4)
                             Sort Key: comment.parent_id
                             Sort Method: external sort  Disk: 80240kB
                             ->  Seq Scan on comment  (cost=0.00..23334.10 rows=1000010 width=72) (actual time=0.012..139.241 rows=1000010 loops=4)
   ->  Sort  (cost=734795.33..778943.48 rows=17659257 width=76) (actual time=2951.336..2951.337 rows=10 loops=1)
         Sort Key: ct.created_date DESC
         Sort Method: top-N heapsort  Memory: 26kB
         ->  CTE Scan on ct  (cost=0.00..353185.14 rows=17659257 width=76) (actual time=0.036..2854.035 rows=1000010 loops=1)
 Planning time: 1.094 ms
 Execution time: 2968.693 ms
(23 rows)

First it seems like it is not using indexes and based on explain.depesz.com it performs bad.

I assume it could be that this is not best table structure design so there is no way to make this performe well or query is not that optimal.

Any suggestions would be appreciated.

2

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.

  • Adding order by and limit to the inner queries that use indexes should be an easy fix. – Colin 't Hart May 12 at 11:10
0

So, I followed suggestions from Evan. Thank you. I added index for created_date DESC and parent_id.

I additionally limited parent search and now it performes even better.

Here is EXPLAIN ANALYZE:

vacuum analyze; EXPLAIN ANALYZE WITH RECURSIVE ct (id, author_id, parent_id, content, depth, updated_date, created_date) AS (
    (
        SELECT id,
               author_id,
               parent_id,
               content,
               0 AS depth,
               updated_date,
               created_date
        FROM comment
        WHERE website_page_id = 1
          AND parent_id IS NULL
          AND deleted_date IS NULL
        LIMIT 10
    )
    UNION
    (
        SELECT comment.id,
               comment.author_id,
               comment.parent_id,
               comment.content,
               depth + 1 AS depth,
               comment.updated_date,
               comment.created_date
        FROM comment
                 JOIN ct ON comment.parent_id = ct.id
    )
) SELECT * FROM ct ORDER BY created_date DESC;
VACUUM

Query plan:

 Sort  (cost=8474.15..8474.20 rows=20 width=76) (actual time=0.051..0.052 rows=10 loops=1)
   Sort Key: ct.created_date DESC
   Sort Method: quicksort  Memory: 26kB
   CTE ct
     ->  Recursive Union  (cost=0.00..8473.32 rows=20 width=77) (actual time=0.013..0.041 rows=10 loops=1)
           ->  Limit  (cost=0.00..0.39 rows=10 width=77) (actual time=0.010..0.014 rows=10 loops=1)
                 ->  Seq Scan on comment  (cost=0.00..39167.00 rows=1000000 width=77) (actual time=0.010..0.012 rows=10 loops=1)
                       Filter: ((parent_id IS NULL) AND (deleted_date IS NULL) AND (website_page_id = 1))
           ->  Nested Loop  (cost=0.42..847.25 rows=1 width=77) (actual time=0.019..0.019 rows=0 loops=1)
                 ->  WorkTable Scan on ct ct_1  (cost=0.00..2.00 rows=100 width=12) (actual time=0.000..0.001 rows=10 loops=1)
                 ->  Index Scan using index_comment_parent_id on comment comment_1  (cost=0.42..8.44 rows=1 width=73) (actual time=0.001..0.001 rows=0 loops=10)
                       Index Cond: (parent_id = ct_1.id)
   ->  CTE Scan on ct  (cost=0.00..0.40 rows=20 width=76) (actual time=0.014..0.046 rows=10 loops=1)
 Planning time: 0.263 ms
 Execution time: 0.093 ms
(15 rows)

Here is query plan analyzer details.

Though I still wonder if this is the best table design for tree of comments.

Any ideas?

  • Im pretty sure you need to have order by created_date desc inside the parent query in order to get the correct results! Otherwise you’re querying the first 10, adding their children, and then returning the 10 newest of all of those. – Colin 't Hart May 12 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.