postgresql, query running slow

Question

The table is rather large (compared to other tables I have). It seems there's index to help sorting for every columns in the query.

What can I do to speed up the query?

(Only thing I can think of is create a view that holds subset of rows, since I need to use recent data only)

explain SELECT "rating_ratinghistory"."id", "rating_ratinghistory"."user_id", "rating_ratinghistory"."content_type_id", "rating_ratinghistory"."object_id", "rating_ratinghistory"."rating_type", "rating_ratinghistory"."time_stamp", "rating_ratinghistory"."score", "rating_ratinghistory"."extra" FROM "rating_ratinghistory" WHERE ("rating_ratinghistory"."content_type_id" = 97 AND "rating_ratinghistory"."object_id" = 2876) ORDER BY "rating_ratinghistory"."id" DESC LIMIT 100;

                                                            QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=6708.03..6708.28 rows=100 width=58)
   ->  Sort  (cost=6708.03..6709.99 rows=783 width=58)
         Sort Key: id DESC
         ->  Bitmap Heap Scan on rating_ratinghistory  (cost=46.34..6678.11 rows=783 width=58)
               Recheck Cond: (object_id = 2876)
               Filter: (content_type_id = 97)
               ->  Bitmap Index Scan on rating_ratinghistory_object_id_7bce52873f671c64_uniq  (cost=0.00..46.14 rows=1827 width=0)
                     Index Cond: (object_id = 2876)
(8 rows)



littlehome_db=# \d+ rating_ratinghistory;
                                                             Table "public.rating_ratinghistory"
     Column      |           Type           | Collation | Nullable |                     Default                      | Storage  | Stats target | Description
-----------------+--------------------------+-----------+----------+--------------------------------------------------+----------+--------------+-------------
 id              | integer                  |           | not null | nextval('rating_ratinghistory_id_seq'::regclass) | plain    |              |
 user_id         | integer                  |           | not null |                                                  | plain    |              |
 content_type_id | integer                  |           | not null |                                                  | plain    |              |
 object_id       | integer                  |           | not null |                                                  | plain    |              |
 rating_type     | smallint                 |           | not null |                                                  | plain    |              |
 score           | smallint                 |           |          |                                                  | plain    |              |
 time_stamp      | timestamp with time zone |           | not null |                                                  | plain    |              |
 extra           | jsonb                    |           |          |                                                  | extended |              |
Indexes:
    "rating_ratinghistory_pkey" PRIMARY KEY, btree (id)
    "rating_ratinghistory_content_type_id" btree (content_type_id)
    "rating_ratinghistory_object_id_7bce52873f671c64_uniq" btree (object_id)
    "rating_ratinghistory_rating_type_5cf76626ba8bfa19_uniq" btree (rating_type)
    "rating_ratinghistory_time_stamp_60ddba37d740eb52_uniq" btree (time_stamp)
    "rating_ratinghistory_user_id" btree (user_id)
Check constraints:
    "rating_ratinghistory_object_id_check" CHECK (object_id >= 0)
Foreign-key constraints:
    "content_type_id_refs_id_47e22c61" FOREIGN KEY (content_type_id) REFERENCES django_content_type(id) DEFERRABLE INITIALLY DEFERRED
    "user_id_refs_id_d2d9be0d" FOREIGN KEY (user_id) REFERENCES custom_auth_customuser(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "rating_ratinghistoryarchive" CONSTRAINT "rating_ratinghistory_ratinghistory_ptr_id_7ee35035_fk_rating_ra" FOREIGN KEY (ratinghistory_ptr_id) REFERENCES rating_ratinghistory(id) DEFERRABLE INITIALLY DEFERRED
Options: autovacuum_vacuum_scale_factor=0.0, autovacuum_analyze_scale_factor=0.0, autovacuum_vacuum_threshold=10000


# SELECT reltuples::bigint AS estimate FROM pg_class where relname='rating_ratinghistory';
 estimate
----------
 16845244
(1 row)


# select * from rating_ratinghistory limit 10;
    id    | user_id | content_type_id | object_id | rating_type | score |          time_stamp           | extra
----------+---------+-----------------+-----------+-------------+-------+-------------------------------+-------
 22846739 |   20298 |             103 |     31111 |          20 |       | 2019-09-15 18:49:49.572748+00 |
 17771225 |  253926 |              97 |      4062 |          40 |    10 | 2019-01-11 05:44:37.224198+00 |
 17771315 |      26 |             103 |     29050 |          20 |       | 2019-02-10 18:41:17.184759+00 |
 17771314 |  253399 |             103 |     29688 |          20 |       | 2019-02-10 18:41:17.187224+00 |
 17771312 |  173965 |             103 |     26608 |          20 |       | 2019-02-10 18:41:17.191079+00 |
 17771311 |  173965 |              97 |      3453 |          20 |       | 2019-02-10 18:41:17.1934+00   |
 17771310 |  251131 |             103 |     25990 |          20 |       | 2019-02-10 18:41:17.195314+00 |
 17771309 |  173965 |             103 |     28727 |          20 |       | 2019-02-10 18:41:17.198409+00 |
 17771308 |  173965 |              97 |      4839 |          20 |       | 2019-02-10 18:41:17.204428+00 |
 17771307 |  251131 |              97 |      2986 |          20 |       | 2019-02-10 18:41:17.207107+00 |

hope now you can see it better, I added the sample data @TimBiegeleisen — eugene
– eugene, Commented May 2, 2020 at 4:44

Tim Biegeleisen · Accepted Answer · 2020-05-02 05:12:48Z

1

I didn't try to muddle through the entire explain plan, but here is the composite index I would suggest for your query:

CREATE INDEX idx ON rating_ratinghistory (content_type_id, object_id, id DESC);

This covers the WHERE clause by including both columns which appear there, and it also includes the id column which appears in ORDER BY.

The main issue I saw when looking at your current indices is that none of them are composite.

edited May 2, 2020 at 5:12

answered May 2, 2020 at 4:55

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

eugene Over a year ago

thanks, should I use (content_type_id, object_id, id desc) since I use ORDER BY "rating_ratinghistory"."id" DESC ?

Tim Biegeleisen Over a year ago

@eugene Use id DESC in the index. Note that Postgres might still be able to use id ASC, because it might able to scan in reverse.

eugene Over a year ago

wow it went from 4000ms down to 40ms , i was not totally unware of composite index... i should read up on it

Tim Biegeleisen Over a year ago

Actually I'm surprised any of your previous indices were being used at all; the Postgres optimizer is starting to get really good. To understand what I suggested in my answer, just learn about how B trees are used.

eugene Over a year ago

I thought i knew how b-tree works, and I remember it working on a 'single' column, so I guess composite index uses b-tree on multiple index, I guess I have to re-visit the b-tree..

|

Collectives™ on Stack Overflow

postgresql, query running slow

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related