# Request time - vote\_count

**URL:** <https://meta.discourse.org/t/request-time-vote-count/67239>\
**Category:** Feature\
**Tags:** topic-voting\
**Created:** [August 1, 2017, 11:46am UTC](https://meta.discourse.org/t/request-time-vote-count/67239 "2017-08-01T11:46:02Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![Stranik](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/stranik/32/85638_2.png) [@Stranik](https://meta.discourse.org/u/Stranik)\
**Post date:** [August 1, 2017, 11:46am UTC](https://meta.discourse.org/t/request-time-vote-count/67239/1 "2017-08-01T11:46:02Z")

</div>

Executing action: latest — **155.30 ms**

`SELECT "topic_custom_fields"."topic_id", "topic_custom_fields"."name", "topic_custom_fields"."value" FROM "topic_custom_fields" WHERE (topic_id in (159,134,1041,958,1037,260,900,1016,1018,773,1015,1034,428,301,998,1005,1006,1008,1012,1010,756,1007,680,1003,781,886,1002,899,662,1000)) AND (name in ('accepted_answer_post_id','vote_count'));`

```
SELECT
  ci.relname
FROM
  pg_index i
  join pg_class ci on ci.oid = i.indexrelid
  join pg_class ct on ct.oid = i.indrelid
WHERE
  ct.relname = 'topics'

```

See indexes

index\_topics\_on\_lower\_title  
index\_topics\_on\_created\_at\_and\_visible  
index\_topics\_on\_pinned\_at  
index\_topics\_on\_pinned\_globally  
idx\_topics\_front\_page  
index\_topics\_on\_id\_and\_deleted\_at  
idx\_topics\_user\_id\_deleted\_at  
index\_topics\_on\_bumped\_at  
topics\_pkey

`CREATE INDEX index_topics_vote_count ON topics (vote_count);`

```
PG::SyntaxError: ERROR: syntax error at or near "CREATE"
LINE 8: CREATE INDEX index_topics_vote_count ON topics (vote_count)

```

Correctly I look? Because of the lack of an index of this speed? And why do I have a mistake?

---

<div class="post-metadata">

**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\
**Post date:** [August 1, 2017, 2:47pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/2 "2017-08-01T14:47:15Z")

</div>

There is already an index on the `topic_id`, `name` pair afaik on the `topic_custom_fields` table.

---

<div class="post-metadata">

**Author:** ![Stranik](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/stranik/32/85638_2.png) [@Stranik](https://meta.discourse.org/u/Stranik)\
**Post date:** [August 1, 2017, 2:59pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/3 "2017-08-01T14:59:20Z")

</div>

And why the other queries is less than 10mc. But this query takes so long? 12 times more than any other.

---

<div class="post-metadata">

**Author:** ![Falco](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/falco/32/179432_2.png) [@Falco](https://meta.discourse.org/u/Falco)\
**Post date:** [August 1, 2017, 3:07pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/4 "2017-08-01T15:07:52Z")

</div>

Can you share the result of

```plaintext
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT "topic_custom_fields"."topic_id", "topic_custom_fields"."name", "topic_custom_fields"."value" FROM "topic_custom_fields" WHERE (topic_id in (159,134,1041,958,1037,260,900,1016,1018,773,1015,1034,428,301,998,1005,1006,1008,1012,1010,756,1007,680,1003,781,886,1002,899,662,1000)) AND (name in ('accepted_answer_post_id','vote_count'));

```

?

---

<div class="post-metadata">

**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\
**Post date:** [August 1, 2017, 3:16pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/5 "2017-08-01T15:16:52Z")

</div>

One possibility may help is covering the index by adding:

```plaintext
create index tmpIndex on topic_custom_fields(topic_id, name, value)

```

Doe s that make it much faster @Stranik? Remember to drop the index after your experiment.

---

<div class="post-metadata">

**Author:** ![Stranik](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/stranik/32/85638_2.png) [@Stranik](https://meta.discourse.org/u/Stranik)\
**Post date:** [August 1, 2017, 3:35pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/6 "2017-08-01T15:35:50Z")

</div>

I’m probably doing something wrong. ( Error.

Executing action: latest — 154.80 ms

`SELECT "topic_custom_fields"."topic_id", "topic_custom_fields"."name", "topic_custom_fields"."value" FROM "topic_custom_fields" WHERE (topic_id in (756,159,134,1041,958,1037,260,900,1016,1018,773,1015,1034,428,301,998,1005,1006,1008,1012,1010,1007,680,1003,781,886,1002,899,662,1000)) AND (name in ('accepted_answer_post_id','vote_count'));`

```
EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)
SELECT "topic_custom_fields"."topic_id", "topic_custom_fields"."name", "topic_custom_fields"."value" FROM "topic_custom_fields" WHERE (topic_id in (756,159,134,1041,958,1037,260,900,1016,1018,773,1015,1034,428,301,998,1005,1006,1008,1012,1010,1007,680,1003,781,886,1002,899,662,1000)) AND (name in ('accepted_answer_post_id','vote_count'));

PG::SyntaxError: ERROR: syntax error at or near "EXPLAIN"
LINE 8: EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON)

```

`create index tmpIndex on topic_custom_fields(topic_id, name, value)`

```
PG::SyntaxError: ERROR: syntax error at or near "create"
LINE 8: create index tmpIndex on topic_custom_fields(topic_id, name,...

```

 ![1](https://global.discourse-cdn.com/meta/original/3X/e/3/e377fef04d3deb212d03783c64f643809017502a.jpg)

---

<div class="post-metadata">

**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\
**Post date:** [August 1, 2017, 3:37pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/7 "2017-08-01T15:37:43Z")

</div>

You can not create indexes via [data explorer](https://meta.discourse.org/t/32566?silent=true) (that is a security feature), you have do it from the console.

---

<div class="post-metadata">

**Author:** ![Stranik](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/stranik/32/85638_2.png) [@Stranik](https://meta.discourse.org/u/Stranik)\
**Post date:** [August 1, 2017, 6:56pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/8 "2017-08-01T18:56:16Z")

</div>

```
[ +
   { +
     "Plan": { +
       "Node Type": "Bitmap Heap Scan", +
       "Relation Name": "topic_custom_fields", +
       "Schema": "public", +
       "Alias": "topic_custom_fields", +
       "Startup Cost": 44.78, +
       "Total Cost": 59.23, +
       "Plan Rows": 25, +
       "Plan Width": 22, +
       "Actual Startup Time": 0.127, +
       "Actual Total Time": 0.184, +
       "Actual Rows": 30, +
       "Actual Loops": 1, +
       "Output": ["topic_id", "name", "value"], +
       "Recheck Cond": "(topic_custom_fields.topic_id = ANY ('{756,159,134,1041,958,1037,260,900,1016,1018,773,1015,1034,428,301,998,1005,1006,1008,1012,1010,1007,680,1003,781,886,1002,899,662,1000}'::integer[]))", +
       "Rows Removed by Index Recheck": 0, +
       "Filter": "((topic_custom_fields.name)::text = ANY ('{accepted_answer_post_id,vote_count}'::text[]))", +
       "Rows Removed by Filter": 9, +
       "Exact Heap Blocks": 9, +
       "Lossy Heap Blocks": 0, +
       "Shared Hit Blocks": 69, +
       "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": "Bitmap Index Scan", +
           "Parent Relationship": "Outer", 

   "Index Name": "tmpindex", +
       "Startup Cost": 0.00, +
       "Total Cost": 44.78, +
       "Plan Rows": 49, +
       "Plan Width": 0, +
       "Actual Startup Time": 0.102, +
       "Actual Total Time": 0.102, +
       "Actual Rows": 39, +
       "Actual Loops": 1, +
       "Index Cond": "(topic_custom_fields.topic_id = ANY ('{756,159,134,1041,958,1037,260,900,1016,1018,773,1015,1034,428,301,998,1005,1006,1008,1012,1010,1007,680,1003,781,886,1002,899,662,1000}'::integer[]))",+
       "Shared Hit Blocks": 60, +
       "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": "Bitmap Index Scan", +
       "Parent Relationship": "Outer", +
       "Index Name": "tmpindex", +
       "Startup Cost": 0.00, +
       "Total Cost": 44.78, +
       "Plan Rows": 49, +
       "Plan Width": 0, +
       "Actual Startup Time": 0.102, +
       "Actual Total Time": 0.102, +
       "Actual Rows": 39, +
       "Actual Loops": 1, +
       "Index Cond": "(topic_custom_fields.topic_id = ANY ('{756,159,134,1041,958,1037,260,900,1016,1018,773,1015,1034,428,301,998,1005,1006,1008,1012,1010,1007,680,1003,781,886,1002,899,662,1000}'::integer[]))",+
       "Shared Hit Blocks": 60, +
       "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.369, +
 "Triggers": [ +
 ], +
 "Execution Time": 0.254 +

```

} +  
]  
(1 row)

(END)

After

`create index tmpIndex on topic_custom_fields(topic_id, name, value)`

Executing action: latest — 134.50 ms (-20ms)

---

<div class="post-metadata">

**Author:** ![Falco](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/falco/32/179432_2.png) [@Falco](https://meta.discourse.org/u/Falco)\
**Post date:** [August 1, 2017, 6:58pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/9 "2017-08-01T18:58:18Z")

</div>

This JSON is invalid, you pasted just the first lines.

---

<div class="post-metadata">

**Author:** ![Stranik](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/stranik/32/85638_2.png) [@Stranik](https://meta.discourse.org/u/Stranik)\
**Post date:** [August 1, 2017, 7:10pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/10 "2017-08-01T19:10:17Z")

</div>

```
----------
 [
         +
   {
         +
     "Plan": {
         +
       "Node Type": "Bitmap Heap Scan",
         +
       "Relation Name": "topic_custom_fields",
         +
       "Schema": "public",
         +
       "Alias": "topic_custom_fields",
         +
       "Startup Cost": 44.78,
         +
       "Total Cost": 59.23,
         +
       "Plan Rows": 25,
         +
       "Plan Width": 22,
         +
       "Actual Startup Time": 0.127,
         +
       "Actual Total Time": 0.184,
         +
       "Actual Rows": 30,
         +
       "Actual Loops": 1,
         +
       "Output": ["topic_id", "name", "value"],
         +
       "Recheck Cond": "(topic_custom_fields.topic_id = ANY ('{756,159,134,1041,958,1037,260,900,1016,1018,773,1015,1034,428,301,998,1005,1006,1008,1012,1010,1007,680,1003,781,886,1002,899,662,1000}'::intege
r[]))", +
       "Rows Removed by Index Recheck": 0,
         +
       "Filter": "((topic_custom_fields.name)::text = ANY ('{accepted_answer_post_id,vote_count}'::text[]))",
         +
       "Rows Removed by Filter": 9,
         +
       "Exact Heap Blocks": 9,
         +
       "Lossy Heap Blocks": 0,
         +
       "Shared Hit Blocks": 69,
         +
       "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": "Bitmap Index Scan", +
           "Parent Relationship": "Outer", +
           "Index Name": "tmpindex", +
           "Startup Cost": 0.00, +
           "Total Cost": 44.78, +
           "Plan Rows": 49, +
           "Plan Width": 0, +
           "Actual Startup Time": 0.102, +
           "Actual Total Time": 0.102, +
           "Actual Rows": 39, +
           "Actual Loops": 1, +
           "Index Cond": "(topic_custom_fields.topic_id = ANY ('{756,159,134,1041,958,1037,260,900,1016,1018,773,1015,1034,428,301,998,1005,1006,1008,1012,1010,1007,680,1003,781,886,1002,899,662,1000}'::integer[]))",+
           "Shared Hit Blocks": 60, +
           "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.369, +
     "Triggers": [ +
     ], +
     "Execution Time": 0.254 +
   } +
 ]
(1 row)

(END)

```

---

<div class="post-metadata">

**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\
**Post date:** [August 1, 2017, 8:16pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/11 "2017-08-01T20:16:05Z")

</div>

I am not sure what exactly is going on here but something is not right about you pg

for us:

```plaintext
SELECT "topic_custom_fields"."topic_id", "topic_custom_fields"."name", "topic_custom_fields"."value" FROM "topic_custom_fields" 
WHERE (topic_id in (67247,66522,26363,67265,67134,67239,67080,67262,67227,67261,67176,33889,54096,17247,66793,67256,67160,66808,30473,67207,67174,46818,35903,67191,67250,67232,65387,67249,55208,15102)) AND 
(name in ('assigned_to_id','accepted_answer_post_id','vote_count'));  

```

This takes **0.7ms**

I recommend you backup your database and restore, it will force a full rebuild of all indexes and such.

---

<div class="post-metadata">

**Author:** ![Stranik](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/stranik/32/85638_2.png) [@Stranik](https://meta.discourse.org/u/Stranik)\
**Post date:** [August 1, 2017, 8:20pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/12 "2017-08-01T20:20:02Z")

</div>

Thanks, I’ll try. Indeed, time is very much.

 ![1](https://global.discourse-cdn.com/meta/original/3X/c/f/cfd1e0c8708adf06c5f18c6caaaeafeea97cef08.jpg)

---

<div class="post-metadata">

**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\
**Post date:** [August 1, 2017, 8:23pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/13 "2017-08-01T20:23:07Z")

</div>

You are reading the wrong number there … the query is taking 1.5ms, the 245 ms is for the gap above it, it is in ruby code.

You can run a flamegraph to see exactly what is going on.

---

<div class="post-metadata">

**Author:** ![Stranik](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/stranik/32/85638_2.png) [@Stranik](https://meta.discourse.org/u/Stranik)\
**Post date:** [August 1, 2017, 9:35pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/14 "2017-08-01T21:35:15Z")

</div>

Thank you! You helped me a lot, topic can be closed. I’m trying to understand the discourse and ruby. )

---

<div class="post-metadata">

**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\
**Post date:** [August 1, 2017, 9:59pm UTC](https://meta.discourse.org/t/request-time-vote-count/67239/15 "2017-08-01T21:59:22Z")

</div>