1

I have a table, account_balance, with primary key (address, timestamp, currency). This table is pretty big, having nearly 1 billion rows and taking ~3T of space. I am doing a simple query on it:

SELECT address, EXISTS(SELECT address FROM account_balance WHERE address = a.address) ex
FROM (VALUES ('ab566dceac0d857dbf05682d1ceb548667cf4580')) AS a(address);

This should be a simple index lookup but instead Postges produces a seq scan. Here's explain result:

Result  (cost=0.37..0.38 rows=1 width=33)
  InitPlan 1 (returns $0)
    ->  Seq Scan on account_balance  (cost=0.00..355882209.06 rows=965801108 width=0)
          Filter: ((address)::text = 'ab566dceac0d857dbf05682d1ceb548667cf4580'::text)

If I set set enable_seqscan = off then as expected:

Result  (cost=0.99..1.00 rows=1 width=33)
  InitPlan 1 (returns $0)
    ->  Index Only Scan using account_balance_pkey on account_balance  (cost=0.83..153234426.09 rows=965801135 width=0)
          Index Cond: (address = 'ab566dceac0d857dbf05682d1ceb548667cf4580'::text)

So the question is - why it chooses the seq scan? It should never make sense to prefer seq scan to index lookup on such a huge table. What could compel it to choose to go sequentially over a 3T table (which of course takes forever) instead of going for the index (which takes microseconds)? Is some parameter wrong or some system variable I need to check? I tried SET random_page_cost = 1 and for some cases it helps, but for others it still insists on using seq scan. Only setting enable_seqscan to off seems to fix it consistently, but I feel it's a wrong approach.

I am not sure how to interpret the costs numbers - I feel like they may be the key for it, but I can't figure out where they come from and why they are what they are.

This is on PostgreSQL 12.

P.S. Tried to do ANALYZE, didn't change anything.

1 Answer 1

1

EXISTS will stop executing as soon as a result is found, so if that happens early on, a sequential scan will be very fast. Obviously PostgreSQL estimates that very many rows in account_balance have that address.

The cost estimate on the sequential scan is misleading, since it is for the complete scan. As you see, the final cost is estimated quite low.

Without EXPLAIN (ANALYZE, BUFFERS) it is impossible to say if PostgreSQL is right, but then you only asked for an explanation why it would choose such a plan.

2
  • Well, the problem is that EXPLAIN ANALYZE for the wrong branch would take forever as it's a 3T DB scan. Getting EXPLAIN ANALYZE for index scan is easy but I'm not sure it's useful without the second one. It is true that seq scan might be very quick, but the probability of it is very low as there are millions of addresses.
    – StasM
    Apr 2, 2021 at 17:13
  • Try SELECT 1 FROM account_balance WHERE address = 'ab566dceac0d857dbf05682d1' LIMIT 1; Sometimes simpler queries are a good idea. Apr 2, 2021 at 18:36

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.