1

I have PostgreSQL database and a table with the following schema:

profiles (
    id serial primary key,
    username varchar(255) unique,
    followers bigint,
    categories citext array not null default '{}'
);

and I have created indices as follows

CREATE INDEX IF NOT EXISTS username_index
    ON profiles(username);
CREATE INDEX IF NOT EXISTS followers_index
    ON profiles(followers);
CREATE INDEX IF NOT EXISTS categories_index
    ON profiles USING GIN(categories);

Creating a GIN index on a citext data type required additional operations which I defined as follows:

CREATE OPERATOR CLASS _citext_ops DEFAULT
  FOR TYPE _citext USING gin AS
  OPERATOR 1 &&(anyarray, anyarray),
  OPERATOR 2 @>(anyarray, anyarray),
  OPERATOR 3 <@(anyarray, anyarray),
  OPERATOR 4 =(anyarray, anyarray),
  FUNCTION 1 citext_cmp(citext, citext),
  FUNCTION 2 ginarrayextract(anyarray, internal, internal),
  FUNCTION 3 ginqueryarrayextract(anyarray, internal, smallint, internal, internal, internal, internal),
  FUNCTION 4 ginarrayconsistent(internal, smallint, anyarray, integer, internal, internal, internal, internal),
  STORAGE citext;

Now, I need to query profiles that have a specific category (one to be precise). This is the query I am using:

SELECT id, username, followers, categories FROM profiles
  WHERE $1 = ANY(categories) AND
        followers is not null
  ORDER BY (followers, id) DESC
  LIMIT 50;

I also ran it with EXPLAIN ANALYZE and this is the output:

       QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..210.57 rows=50 width=70) (actual time=95099.206..96187.302 rows=50 loops=1)
   ->  Index Scan Backward using followers_index on profiles  (cost=0.43..4236073.48 rows=1007921 width=70) (actual time=95099.202..96187.206 rows=50 loops=1)
         Filter: ('food'::citext = ANY (categories))
         Rows Removed by Filter: 80711
 Planning time: 387.986 ms
 Execution time: 96187.590 ms
(6 rows)

As seen in this output the executor uses an index scan, as expected. The query ran on a hot standby replication server, I wonder if that is the cause of the slowdown.

EDIT: Actually it turns out that the problem is indeed the hot standby replica. The same query on master runs in 100ms, with the same query plan:

                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.43..198.22 rows=50 width=70) (actual time=97.456..100.930 rows=50 loops=1)
   ->  Index Scan Backward using followers_index on profiles  (cost=0.43..6080117.69 rows=1537024 width=70) (actual time=97.454..100.920 rows=50 loops=1)
         Filter: ('food'::citext = ANY (categories))
         Rows Removed by Filter: 80672
 Planning time: 0.196 ms
 Execution time: 100.957 ms
(6 rows)
  • 2
    Does the query modification detailed in the answers to stackoverflow.com/questions/18082625/… help at all? I'd be tempted to redesign and have a separate table that contain profile,category tuples – Philᵀᴹ Jun 28 '18 at 8:04
  • @Philᵀᴹ Performance is pretty much identical. Actually, the problem is indeed the hot standby replica. I ran the query on master and it took 100ms (I added the plan to the question). As far as I know queries on the hot standby server should run identically as on the master server. – rubik Jun 28 '18 at 8:26
  • What version of PostgreSQL? – jjanes Jun 28 '18 at 16:32
  • Seeing explain (analyze, buffers) on each system could help diagnose this. – jjanes Jun 28 '18 at 16:48

Your Answer

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

Browse other questions tagged or ask your own question.