This is the story if a nightmare... I have this table:

                                        Table "subscriptions"
   Column   |            Type             | Collation | Nullable |                  Default                  
 id         | bigint                      |           | not null | nextval('subscriptions_id_seq'::regclass)
 project_id | integer                     |           |          | 
 endpoint   | character varying           |           |          | 
 created_at | timestamp without time zone |           | not null | 
 uid        | character varying           |           |          | 
 tags       | character varying[]         |           |          | '{}'::character varying[]
 trashed_at | timestamp without time zone |           |          | 

    "subscriptions_pkey" PRIMARY KEY, btree (id)
    "index_subscriptions_on_endpoint" btree (endpoint)
    "index_subscriptions_on_project_id_and_created_at" btree (project_id, created_at DESC)
    "index_subscriptions_on_project_id_and_tags" gin (project_id, tags) WHERE trashed_at IS NULL
    "index_subscriptions_on_project_id_and_tags_using_btree" btree (project_id, tags) WHERE trashed_at IS NULL
    "index_subscriptions_on_project_id_and_trashed_at" btree (project_id, trashed_at DESC)
    "index_subscriptions_on_project_id_and_uid" btree (project_id, uid) WHERE trashed_at IS NULL

I have this query:

SELECT "subscriptions".* 
FROM "subscriptions" 
WHERE "subscriptions"."project_id" = 12345 
  AND "subscriptions"."trashed_at" IS NULL 
  AND ((tags @> ARRAY['crt:2020_02']::varchar[])) 
ORDER BY "subscriptions"."created_at" DESC 

Note: It's a SaaS that produces the query on the db based on user inputs (sent through a REST API), so I don't have full control on the tags used for filtering (which can also include AND, OR and NOT conditions). So please don't focus on this exact tag filter: just consider that there are some conditions on tags (variable). Also note that tags are not pre-defined: they are attached to the rows on the fly by the customer and I don't have control on tags (they can be any string).

This is the result:

          QUERY PLAN                                                                                          
 Limit  (cost=0.56..13577.18 rows=30 width=382) (actual time=1195740.956..1195740.956 rows=0 loops=1)
   Buffers: shared hit=5918464 read=3226736 dirtied=1537
   ->  Index Scan using index_subscriptions_on_project_id_and_created_at on subscriptions  (cost=0.56..5556910.68 rows=12279 width=382) (actual time=1195740.951..1195740.951 rows=0 loops=1)
         Index Cond: (project_id = 12345)
         Filter: ((trashed_at IS NULL) AND (tags @> '{crt:2020_02}'::character varying[]))
         Rows Removed by Filter: 9202438
         Buffers: shared hit=5918464 read=3226736 dirtied=1537
 Planning Time: 4.319 ms
 Execution Time: 1195741.912 ms
(9 rows)

As you can see this simple query takes several minutes!

I have tried the following:

Forcing use of a different index

I simply change the ORDER BY to this:

ORDER BY ("subscriptions"."created_at" + interval '0 days') DESC

And the time goes from several minutes to a few ms:

                                                                                QUERY PLAN                                                                                
 Limit  (cost=63775.08..63775.16 rows=30 width=391) (actual time=566.728..566.728 rows=0 loops=1)
   Buffers: shared hit=5375 read=2492
   ->  Sort  (cost=63775.08..63856.39 rows=32522 width=391) (actual time=566.727..566.727 rows=0 loops=1)
         Sort Key: ((created_at + '00:00:00'::interval)) DESC
         Sort Method: quicksort  Memory: 25kB
         Buffers: shared hit=5375 read=2492
         ->  Bitmap Heap Scan on subscriptions  (cost=1200.28..62814.56 rows=32522 width=391) (actual time=566.709..566.709 rows=0 loops=1)
               Recheck Cond: ((project_id = 12345) AND (tags @> '{crt:2020_02}'::character varying[]) AND (trashed_at IS NULL))
               Buffers: shared hit=5372 read=2492
               ->  Bitmap Index Scan on index_subscriptions_on_project_id_and_tags  (cost=0.00..1192.15 rows=32522 width=0) (actual time=566.706..566.706 rows=0 loops=1)
                     Index Cond: ((project_id = 12345) AND (tags @> '{crt:2020_02}'::character varying[]))
                     Buffers: shared hit=5372 read=2492
 Planning Time: 2.511 ms
 Execution Time: 566.827 ms
(14 rows)

The problem is that it is a hack and produces terrible results and even seq scan when thousands of rows match the condition.

However it shows that PG could choose a better plan!

Improving stats

In order to have better plans you try this:

ALTER TABLE subscriptions ALTER project_id SET STATISTICS 10000;
ALTER TABLE subscriptions ALTER tags SET STATISTICS 10000;

CREATE STATISTICS stats_on_subscriptions ON project_id, tags FROM subscriptions;

VACUUM ANALYZE subscriptions;

Nothing changes.

Creating the perfect indexes

I have tried (only one at a time) the following indexes:

CREATE INDEX CONCURRENTLY idx_subscriotion_fields_1 ON subscriptions (created_at DESC, project_id, tags) WHERE trashed_at IS NULL;

CREATE INDEX CONCURRENTLY idx_subscriotion_fields_2 ON subscriptions (project_id, created_at DESC, tags) WHERE trashed_at IS NULL;

CREATE INDEX CONCURRENTLY idx_subscriotion_fields_3 ON subscriptions (project_id, tags, created_at DESC) WHERE trashed_at IS NULL;

However they are completely ignored by PG: the planner keeps using index_subscriptions_on_project_id_and_created_at. I have really no idea why: I think that idx_subscriotion_fields_1 would be a perfect choice for most queries (whether they return a few rows or many).

I have even tried to temporary disable index_subscriptions_on_project_id_and_created_at: instead of using the other indexes PG chooses a seq scan!

Improving other settings

I have tried to make frequent autovacuum and setting random_page_cost = 1. Nothing changes.

Control the plan from application level

I have also tried to search whether I can use index hints, but unfortunately PG doesn't allow them :( It's frustrating. Otherwise, from my application, I could use a count (which is fast for the same queries) and point PG in the right direction.

Final thoughts

Filtering some rows on tags and taking the most recent items doesn't seems a complex need.

However on this large table PG has many issues and I can't find a solution. I start thinking that PG has several missing features (good statistics on array values, index hint, ordered btree indexes on array values, support for multitenancy, etc.) and is not suited for big data. I hope that I am wrong since we have built the entire SaaS on PG. Every post keeps saying the same things that I have already tried: any help would be greatly appreciated! Thank you!

  • 1
    I am not sure how well PostgreSQL can estimate @> searches on jsonb columns. The non-normalized data model sure doesn't help, so if something is not suited for big data, I'd point at the model. No other database I have heard of has better indexing features for JSON data. If you can estimate beforehand what would be the best choice, why don't you modify the ORDER BY clause depending on the outcome? The hack is just as hacky as a query hint. Commented Feb 28, 2020 at 11:37
  • @LaurenzAlbe Thanks for the comment. You are not the first to tell that non-normalized data doesn't help... however that structure in my case makes many operation easier and updates fast. It makes sense. Also the index structure should be decoupled from the data structure (e.g. you can even define indexes over functions). I don't understand why PG released array support if statistics doesn't work properly and indexing is hard (and doesn't even allow hints because it is sure that it makes the best choices).
    – collimarco
    Commented Feb 28, 2020 at 13:39
  • There is a wide margin between "works properly" and "magically guesses everything correctly", which is what you would like. If you think that query performance and indexing are independent ("decoupled") from your data model, that is wishful thinking. You have a "hint": the ORDER BY clause you are using. There is no kind of "conditional query hint" in any database I am aware of. Commented Feb 28, 2020 at 13:45
  • 1
    What if you wrap it into a CTE? pastebin.com/cTFH6sn3 as the inner query is planned independently from the outer query in Postgres 11 (or in 12 with the materialized option). This might result in a stable plan even without the "order by trick"
    – user1822
    Commented Feb 28, 2020 at 14:22
  • 1
    @ypercubeᵀᴹ: tags is an array, no good like this in a btree index. The core problem of the query is the same as in your classic dba.stackexchange.com/q/18300/3684 . Array instead of range. (Plus, OFFSET is a bad idea in addition.) Commented Mar 2, 2020 at 13:59


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.