I have following simple table that represents financial transactions:

CREATE TABLE transaction (
    "user_id" text not null,
    "offset" serial not null,
    "tx_data" text not null

CREATE INDEX transaction_offset on transaction("offset");
CREATE INDEX transaction_user_offset on transaction("user_id", "offset");
  • Table contains ~billion rows.
  • There are few users which account for most of the transactions (taken from pg_stats):
most_common_vals: {'userA', 'userB', .... 'userC'}
most_common_freq: {0.6, 0.07, ...., 0.00001}
  • First transaction for userA is at offset 2_000_000
  • Transactions for users are not distributed evenly. Even though userA has a lot of transactions those transactions are "batched" close to each other. There are period of times when user was very active and then there are period of times when it was not active at all.


If I simply try to query first 100 transactions by user:

select * from transaction where user_id = 'someUser' order by "offset" limit 100

If I query by userC (which accounts for only 0.0001 of transactions) it's very fast and uses transaction_user_offset_idx index:

Limit (cost=0.71..370.76 rows=100 width=294)
-> Index Scan using transaction_user_offset_idx on transaction (cost=0.71..112511575.20 rows=30404045 width=294)
Index Cond: (user_id = 'userC')

If I query by userA (which accounts for 60% of transactions) it's extremly slow. Query planner uses only transaction_offset as it figures out since userA is so frequent it doesn't make sense to use any index for user and just filter sequentially:

Limit (cost=0.58..11.36 rows=100 width=294)
-> Index Scan using transaction_offset_idx on transaction (cost=0.58..359836920.47 rows=401760686 width=294)
Filter: (user_id = 'userA')

It is so slow because table is very big and the first row for userA is at offset 2_000_000. Query therefore has to traverse 2mln rows sequentially before it gets to the first row for userA.

db-fiddle to reproduce an issue

Solutions I tried

  • Lowering random_page_cost. I don't like this solution as there are for sure places where sequential scans might have been more optimal. On top of that even if this param is set to 1.0 frequency of userA is so high query planner still chooses to use filter.
  • Disabling planner statistics completely for user column. This works for this query (it always uses transaction_user_offset index) but it feels "hackish" and I can't predict how it will affect other queries.

What other possible solutions are out there?

  • Has your table been vacuumed and analyzed recently?
    – jjanes
    Aug 2, 2022 at 15:11
  • "Disabling planner statistics completely for user column" How?
    – jjanes
    Aug 2, 2022 at 15:11
  • Yes. It has been vacummed and analyzed manually. AFAIK You can disable planner statistics for column by setting alter table set STATISTICS 0 + manually removing existing row from pg_statistics table.
    – jdziwbc
    Aug 2, 2022 at 15:12
  • Fixed. Thanks for suggestions.
    – jdziwbc
    Aug 2, 2022 at 15:26
  • Do you really need to select all columns? Do you still see the problem if you select just the columns you need? Is the combination of user_id and "offset" unique/primary?
    – jjanes
    Aug 2, 2022 at 15:53

2 Answers 2


A more localized solution than munging the entire columns stats just for the sake of one query is to hide the actual constant from the planner at planning time, by putting it into a dummy subselect.

select * from transaction 
where "user_id" = (select 'userA') 
order by "offset" limit 100;

I don't understand why the planner doesn't do the right thing in the first place, though. I can sort of reproduce it, but for me, lowering random_page_cost to 1.1 is enough to get back to the right plan (and also happens to make sense given my hardware)

[Edit] I've explored a bit more on what is going on the planner. I think you already know the gross error, it doesn't know how the ordering of user_id and "offet" are related. It thinks that to find 100 qualifying rows it only needs to scan 100/0.6 = 160 rows of the transaction_offset index, rather than 2000100 like it does. But still, that would leave that index slightly more expensive, not less expensive, than the better index. But that one-column index is smaller than the two-column index, and the index size does play a role in the cost estimation. A weak role, but enough to tip the balance.

This does leave me with a naughty thought. If you append NUL bytes (ascii-0) to the transaction_offset index until it is the same size as the transaction_user_offset index, it should remove this total size difference as a factor in the planning. In my hands this is enough to get the planner to start making the correct decisions again. This is obviously not something which should be done cavalierly on a production system.

  • Thanks a lot. This trick indeed forces planer to ignore statistics. Here is the reproduces issue - db-fiddle.com/f/3yHn9xvouKBz8XbAcuZrjh/1. Indeed setting random_page_cost below 1.5 makes planer to always use (user, offset) index. I wonder why it's not the case in our prod db. Will have to investigate what are the other factors when it comes to cost calculation.
    – jdziwbc
    Aug 2, 2022 at 18:06

You can force usage of a generic plan by using extended protocol / prepared statements and setting plan_cache_mode = force_generic_plan. This way it shouldn't take into account specific user_id and most likely will use the index

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.