Request time - vote_count

Feature
1

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?

2

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

3

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

4

Can you share the result of

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'));

?

1 Like
5

One possibility may help is covering the index by adding:

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.

1 Like
6

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
1.jpg972×692 40.8 KB

7

You can not create indexes via data explorer (that is a security feature), you have do it from the console.

4 Likes
8 
[                                                                                                                                                                                                                      +
   {                                                                                                                                                                                                                    +
     "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)

9

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

1 Like
10 
----------
 [
         +
   {
         +
     "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)
11

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

for us:

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.

1 Like
12

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

1
1.jpg1532×576 94.4 KB

13

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.

3 Likes
14

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

2 Likes