GET /api/topics - DB query performance issues (rahvaalgatus.ee) #100

tiblu · 2019-01-22T13:41:22Z

Overview

Rahvaalgatus.ee is running into performance issues querying public Topics (GET /api/topics).

What we know

The code that does the query:
- https://github.com/rahvaalgatus/rahvaalgatus/blob/master/lib/citizenos_api.js#L40
- They are doing 4 queries for a page load - https://github.com/rahvaalgatus/rahvaalgatus/blob/5883a609ada7f810a6c900e44dbb9807c634bc11/controllers/initiatives_controller.js#L46
DB query itself

CREATE OR REPLACE FUNCTION pg_temp.delegations(uuid)
   RETURNS TABLE("voteId" uuid, "toUserId" uuid, "byUserId" uuid, depth INT)
       AS $$
           WITH  RECURSIVE q ("voteId", "toUserId", "byUserId", depth)
               AS
                   (
                   SELECT
                       vd."voteId",
                       vd."toUserId",
                       vd."byUserId",
                       1
                   FROM "VoteDelegations" vd
                   WHERE vd."voteId" = $1

                   AND vd."deletedAt" IS NULL
                   UNION ALL
                   SELECT
                       vd."voteId",
                       vd."toUserId",
                       dc."byUserId",
                       dc.depth+1
                   FROM q dc, "VoteDelegations" vd
                   WHERE vd."byUserId" = dc."toUserId"
                     AND vd."voteId" = dc."voteId"
                     AND vd."deletedAt" IS NULL
                   )
   SELECT * FROM q; $$
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION pg_temp.indirect_delegations(uuid)
   RETURNS TABLE("voteId" uuid, "toUserId" uuid, "byUserId" uuid, depth int)
       AS $$
           SELECT DISTINCT ON("byUserId")
               "voteId",
               "toUserId",
               "byUserId",
               depth
           FROM pg_temp.delegations($1)
           ORDER BY "byUserId", depth DESC; $$
   LANGUAGE SQL;

CREATE OR REPLACE FUNCTION pg_temp.vote_groups(uuid)
   RETURNS TABLE ("voteId" uuid, "userId" uuid, "optionGroupId" character varying , "updatedAt" timestamp with time zone)
   AS $$

   SELECT DISTINCT ON("voteId","userId")
           vl."voteId",
           vl."userId",
           vl."optionGroupId",
           vl."updatedAt"
       FROM "VoteLists" vl
       WHERE vl."voteId" = $1
         AND vl."deletedAt" IS NULL
       ORDER BY "voteId", "userId", "createdAt" DESC, "optionGroupId" ASC; $$
   LANGUAGE SQL;

CREATE OR REPLACE FUNCTION pg_temp.votes(uuid)
   RETURNS TABLE ("voteId" uuid, "userId" uuid, "optionId" uuid, "optionGroupId" character varying)
   AS $$
       SELECT
vl."voteId",
           vl."userId",
           vl."optionId",
           vl."optionGroupId"
       FROM "VoteLists" vl
       JOIN pg_temp.vote_groups($1) vg ON (vl."voteId" = vg."voteId" AND vl."userId" = vg."userId" AND vl."optionGroupId" = vg."optionGroupId")
       WHERE vl."voteId" = $1; $$
   LANGUAGE SQL;

CREATE OR REPLACE FUNCTION pg_temp.votes_with_delegations(uuid)
   RETURNS TABLE ("voteId" uuid, "userId" uuid, "optionId" uuid, depth int)
   AS $$
       SELECT
           v."voteId",
           v."userId",
           v."optionId",

         id."depth"
       FROM pg_temp.votes($1) v
       LEFT JOIN pg_temp.indirect_delegations($1) id ON (v."userId" = id."toUserId")
       WHERE v."userId" NOT IN (SELECT "byUserId" FROM pg_temp.indirect_delegations($1) WHERE "voteId"=v."voteId"); $$
   LANGUAGE SQL;

CREATE OR REPLACE FUNCTION pg_temp.get_vote_results (uuid)
   RETURNS TABLE ("voteCount" bigint, "optionId" uuid, "voteId" uuid)
   AS $$
       SELECT
           COUNT(v."optionId") + 1 as "voteCount",
           v."optionId",
           v."voteId"
       FROM pg_temp.votes_with_delegations($1) v

   WHERE v.depth IS NOT NULL
       GROUP BY v."optionId", v."voteId"
                                       UNION ALL
                                       SELECT
           COUNT(v."optionId") as "voteCount",
           v."optionId",
           v."voteId"
       FROM pg_temp.votes_with_delegations($1) v
       WHERE v.depth IS NULL
       GROUP BY v."optionId", v."voteId"; $$
   LANGUAGE SQL;

SELECT
   SUM(v."voteCount") as "voteCount",
   v."optionId",
   v."voteId",

vo."value"
                            FROM "Topics" t
LEFT JOIN "TopicVotes" tv
   ON tv."topicId" = t.id AND tv."deletedAt" IS NULL
LEFT JOIN pg_temp.get_vote_results(tv."voteId") v ON v."voteId" = tv."voteId"
LEFT JOIN "VoteOptions" vo ON v."optionId" = vo.id
                        WHERE  t."deletedAt" IS NULL
AND v."optionId" IS NOT NULL
AND v."voteId" IS NOT NULL
AND vo."value" IS NOT NULL
AND t.visibility = 'public'
GROUP BY v."optionId",v."voteId", vo."value";

DB query explain plan, we see an expensive function scan (get_vote_results) there:

[
  {
    "Plan": {
      "Node Type": "Aggregate",
      "Strategy": "Hashed",
      "Partial Mode": "Simple",
      "Parallel Aware": false,
      "Startup Cost": 611.00,
      "Total Cost": 613.66,
      "Plan Rows": 213,
      "Plan Width": 67,
      "Actual Startup Time": 989.702,
      "Actual Total Time": 989.796,
      "Actual Rows": 106,
      "Actual Loops": 1,
      "Output": ["sum(v.\"voteCount\")", "v.\"optionId\"", "v.\"voteId\"", "vo.value"],
      "Group Key": ["v.\"optionId\"", "v.\"voteId\"", "vo.value"],
      "Shared Hit Blocks": 516875,
      "Shared Read Blocks": 0,
      "Shared Dirtied Blocks": 0,
      "Shared Written Blocks": 0,
      "Local Hit Blocks": 0,
      "Local Read Blocks": 0,
      "Local Dirtied Blocks": 0,
      "Local Written Blocks": 0,
      "Temp Read Blocks": 0,
      "Temp Written Blocks": 0,
      "Plans": [
        {
          "Node Type": "Hash Join",
          "Parent Relationship": "Outer",
          "Parallel Aware": false,
          "Join Type": "Inner",
          "Startup Cost": 14.09,
          "Total Cost": 608.87,
          "Plan Rows": 213,
          "Plan Width": 43,
          "Actual Startup Time": 6.248,
          "Actual Total Time": 989.454,
          "Actual Rows": 106,
          "Actual Loops": 1,
          "Output": ["v.\"optionId\"", "v.\"voteId\"", "vo.value", "v.\"voteCount\""],
          "Inner Unique": true,
          "Hash Cond": "(v.\"optionId\" = vo.id)",
          "Shared Hit Blocks": 516875,
          "Shared Read Blocks": 0,
          "Shared Dirtied Blocks": 0,
          "Shared Written Blocks": 0,
          "Local Hit Blocks": 0,
          "Local Read Blocks": 0,
          "Local Dirtied Blocks": 0,
          "Local Written Blocks": 0,
          "Temp Read Blocks": 0,
          "Temp Written Blocks": 0,
          "Plans": [
            {
              "Node Type": "Nested Loop",
              "Parent Relationship": "Outer",
              "Parallel Aware": false,
              "Join Type": "Inner",
              "Startup Cost": 4.93,
              "Total Cost": 599.13,
              "Plan Rows": 213,
              "Plan Width": 40,
              "Actual Startup Time": 5.898,
              "Actual Total Time": 988.871,
              "Actual Rows": 106,
              "Actual Loops": 1,
              "Output": ["v.\"voteCount\"", "v.\"optionId\"", "v.\"voteId\""],
              "Inner Unique": false,
              "Shared Hit Blocks": 516872,
              "Shared Read Blocks": 0,
              "Shared Dirtied Blocks": 0,
              "Shared Written Blocks": 0,
              "Local Hit Blocks": 0,
              "Local Read Blocks": 0,
              "Local Dirtied Blocks": 0,
              "Local Written Blocks": 0,
              "Temp Read Blocks": 0,
              "Temp Written Blocks": 0,
              "Plans": [
                {
                  "Node Type": "Hash Join",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Join Type": "Inner",
                  "Startup Cost": 4.68,
                  "Total Cost": 59.23,
                  "Plan Rows": 43,
                  "Plan Width": 16,
                  "Actual Startup Time": 0.180,
                  "Actual Total Time": 0.816,
                  "Actual Rows": 83,
                  "Actual Loops": 1,
                  "Output": ["tv.\"voteId\""],
                  "Inner Unique": false,
                  "Hash Cond": "(t.id = tv.\"topicId\")",
                  "Shared Hit Blocks": 50,
                  "Shared Read Blocks": 0,
                  "Shared Dirtied Blocks": 0,
                  "Shared Written Blocks": 0,
                  "Local Hit Blocks": 0,
                  "Local Read Blocks": 0,
                  "Local Dirtied Blocks": 0,
                  "Local Written Blocks": 0,
                  "Temp Read Blocks": 0,
                  "Temp Written Blocks": 0,
                  "Plans": [
                    {
                      "Node Type": "Seq Scan",
                      "Parent Relationship": "Outer",
                      "Parallel Aware": false,
                      "Relation Name": "Topics",
                      "Schema": "public",
                      "Alias": "t",
                      "Startup Cost": 0.00,
                      "Total Cost": 53.52,
                      "Plan Rows": 160,
                      "Plan Width": 16,
                      "Actual Startup Time": 0.009,
                      "Actual Total Time": 0.426,
                      "Actual Rows": 175,
                      "Actual Loops": 1,
                      "Output": ["t.id", "t.title", "t.description", "t.status", "t.visibility", "t.\"creatorId\"", "t.\"createdAt\"", "t.\"updatedAt\"", "t.\"deletedAt\"", "t.categories", "t.\"tokenJoin\"", "t.\"padUrl\"", "t.\"endsAt\"", "t.\"sourcePartnerId\"", "t.hashtag", "t.\"sourcePartnerObjectId\""],
                      "Filter": "((t.\"deletedAt\" IS NULL) AND (t.visibility = 'public'::\"enum_Topics_visibility\"))",
                      "Rows Removed by Filter": 259,
                      "Shared Hit Blocks": 48,
                      "Shared Read Blocks": 0,
                      "Shared Dirtied Blocks": 0,
                      "Shared Written Blocks": 0,
                      "Local Hit Blocks": 0,
                      "Local Read Blocks": 0,
                      "Local Dirtied Blocks": 0,
                      "Local Written Blocks": 0,
                      "Temp Read Blocks": 0,
                      "Temp Written Blocks": 0
                    },
                    {
                      "Node Type": "Hash",
                      "Parent Relationship": "Inner",
                      "Parallel Aware": false,
                      "Startup Cost": 3.19,
                      "Total Cost": 3.19,
                      "Plan Rows": 119,
                      "Plan Width": 32,
                      "Actual Startup Time": 0.154,
                      "Actual Total Time": 0.155,
                      "Actual Rows": 120,
                      "Actual Loops": 1,
                      "Output": ["tv.\"voteId\"", "tv.\"topicId\""],
                      "Hash Buckets": 1024,
                      "Original Hash Buckets": 1024,
                      "Hash Batches": 1,
                      "Original Hash Batches": 1,
                      "Peak Memory Usage": 16,
                      "Shared Hit Blocks": 2,
                      "Shared Read Blocks": 0,
                      "Shared Dirtied Blocks": 0,
                      "Shared Written Blocks": 0,
                      "Local Hit Blocks": 0,
                      "Local Read Blocks": 0,
                      "Local Dirtied Blocks": 0,
                      "Local Written Blocks": 0,
                      "Temp Read Blocks": 0,
                      "Temp Written Blocks": 0,
                      "Plans": [
                        {
                          "Node Type": "Seq Scan",
                          "Parent Relationship": "Outer",
                          "Parallel Aware": false,
                          "Relation Name": "TopicVotes",
                          "Schema": "public",
                          "Alias": "tv",
                          "Startup Cost": 0.00,
                          "Total Cost": 3.19,
                          "Plan Rows": 119,
                          "Plan Width": 32,
                          "Actual Startup Time": 0.006,
                          "Actual Total Time": 0.076,
                          "Actual Rows": 120,
                          "Actual Loops": 1,
                          "Output": ["tv.\"voteId\"", "tv.\"topicId\""],
                          "Filter": "(tv.\"deletedAt\" IS NULL)",
                          "Rows Removed by Filter": 0,
                          "Shared Hit Blocks": 2,
                          "Shared Read Blocks": 0,
                          "Shared Dirtied Blocks": 0,
                          "Shared Written Blocks": 0,
                          "Local Hit Blocks": 0,
                          "Local Read Blocks": 0,
                          "Local Dirtied Blocks": 0,
                          "Local Written Blocks": 0,
                          "Temp Read Blocks": 0,
                          "Temp Written Blocks": 0
                        }
                      ]
                    }
                  ]
                },
                {
                  "Node Type": "Function Scan",
                  "Parent Relationship": "Inner",
                  "Parallel Aware": false,
                  "Function Name": "get_vote_results",
                  "Schema": "pg_temp_5",
                  "Alias": "v",
                  "Startup Cost": 0.25,
                  "Total Cost": 12.75,
                  "Plan Rows": 5,
                  "Plan Width": 40,
                  "Actual Startup Time": 11.900,
                  "Actual Total Time": 11.900,
                  "Actual Rows": 1,
                  "Actual Loops": 83,
                  "Output": ["v.\"voteCount\"", "v.\"optionId\"", "v.\"voteId\""],
                  "Function Call": "pg_temp_5.get_vote_results(tv.\"voteId\")",
                  "Filter": "((v.\"optionId\" IS NOT NULL) AND (v.\"voteId\" IS NOT NULL) AND (tv.\"voteId\" = v.\"voteId\"))",
                  "Rows Removed by Filter": 0,
                  "Shared Hit Blocks": 516822,
                  "Shared Read Blocks": 0,
                  "Shared Dirtied Blocks": 0,
                  "Shared Written Blocks": 0,
                  "Local Hit Blocks": 0,
                  "Local Read Blocks": 0,
                  "Local Dirtied Blocks": 0,
                  "Local Written Blocks": 0,
                  "Temp Read Blocks": 0,
                  "Temp Written Blocks": 0
                }
              ]
            },
            {
              "Node Type": "Hash",
              "Parent Relationship": "Inner",
              "Parallel Aware": false,
              "Startup Cost": 5.74,
              "Total Cost": 5.74,
              "Plan Rows": 274,
              "Plan Width": 19,
              "Actual Startup Time": 0.343,
              "Actual Total Time": 0.344,
              "Actual Rows": 276,
              "Actual Loops": 1,
              "Output": ["vo.value", "vo.id"],
              "Hash Buckets": 1024,
              "Original Hash Buckets": 1024,
              "Hash Batches": 1,
              "Original Hash Batches": 1,
              "Peak Memory Usage": 22,
              "Shared Hit Blocks": 3,
              "Shared Read Blocks": 0,
              "Shared Dirtied Blocks": 0,
              "Shared Written Blocks": 0,
              "Local Hit Blocks": 0,
              "Local Read Blocks": 0,
              "Local Dirtied Blocks": 0,
              "Local Written Blocks": 0,
              "Temp Read Blocks": 0,
              "Temp Written Blocks": 0,
              "Plans": [
                {
                  "Node Type": "Seq Scan",
                  "Parent Relationship": "Outer",
                  "Parallel Aware": false,
                  "Relation Name": "VoteOptions",
                  "Schema": "public",
                  "Alias": "vo",
                  "Startup Cost": 0.00,
                  "Total Cost": 5.74,
                  "Plan Rows": 274,
                  "Plan Width": 19,
                  "Actual Startup Time": 0.008,
                  "Actual Total Time": 0.170,
                  "Actual Rows": 276,
                  "Actual Loops": 1,
                  "Output": ["vo.value", "vo.id"],
                  "Filter": "(vo.value IS NOT NULL)",
                  "Rows Removed by Filter": 0,
                  "Shared Hit Blocks": 3,
                  "Shared Read Blocks": 0,
                  "Shared Dirtied Blocks": 0,
                  "Shared Written Blocks": 0,
                  "Local Hit Blocks": 0,
                  "Local Read Blocks": 0,
                  "Local Dirtied Blocks": 0,
                  "Local Written Blocks": 0,
                  "Temp Read Blocks": 0,
                  "Temp Written Blocks": 0
                }
              ]
            }
          ]
        }
      ]
    },
    "Planning Time": 0.439,
    "Triggers": [
    ],
    "Execution Time": 989.940
  }
]

Related to:

Voting - creates multiple VoteList lines and is not idempotent #69

The text was updated successfully, but these errors were encountered:

loorm · 2019-06-17T07:17:34Z

Triage #6. Assigned medium priority, because users are currently not directly affected.

tiblu · 2019-09-09T13:14:39Z

Related to:

Voting - creates multiple VoteList lines and is not idempotent #69 - if there is only one VoteLists line, we can remove some part of the query thus improving performance.

KatiVellak · 2020-04-10T15:01:17Z

Legally reviewed, no impact.

tiblu · 2020-05-27T14:28:37Z

RAA.ee has solved the issue for them selves, we have no problem with the query and have had few rewrites.

moll mentioned this issue Jan 22, 2019

Esileht ning algatuste lehed aeglased rahvaalgatus/rahvaalgatus#129

Closed

tiblu added the bug Existing feature not working as designed. label Jun 4, 2019

loorm mentioned this issue Sep 18, 2019

Voting - creates multiple VoteList lines and is not idempotent #69

Closed

loorm added bug Existing feature not working as designed. and removed bug Existing feature not working as designed. labels Nov 12, 2019

loorm added the low priority label Feb 17, 2020

loorm removed the low priority label Feb 28, 2020

loorm changed the title ~~API: GET /api/topics - DB query performance issues (rahvaalgatus.ee)~~ GET /api/topics - DB query performance issues (rahvaalgatus.ee) Mar 2, 2020

loorm added API API issue. Needs dev time. and removed bug Existing feature not working as designed. labels Mar 2, 2020

tiblu closed this as completed May 27, 2020

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

GET /api/topics - DB query performance issues (rahvaalgatus.ee) #100

GET /api/topics - DB query performance issues (rahvaalgatus.ee) #100

tiblu commented Jan 22, 2019 •

edited

Loading

loorm commented Jun 17, 2019

tiblu commented Sep 9, 2019

KatiVellak commented Apr 10, 2020

tiblu commented May 27, 2020

GET /api/topics - DB query performance issues (rahvaalgatus.ee) #100

GET /api/topics - DB query performance issues (rahvaalgatus.ee) #100

Comments

tiblu commented Jan 22, 2019 • edited Loading

loorm commented Jun 17, 2019

tiblu commented Sep 9, 2019

KatiVellak commented Apr 10, 2020

tiblu commented May 27, 2020

tiblu commented Jan 22, 2019 •

edited

Loading