0

I have a PostgreSQL table defined with a unique constraint on 4 columns:

create table public.values (
  id bigint primary key generated always as identity,
  a smallint not null references .... (id),
  b smallint not null,
  c smallint not null,
  d int not null references .... (id),
  -- ... some other cols
  constraint unique_cols unique (a, b, c, d)
);

create index values_d on public.values using btree (dasc);

And this table has a lot of rows (it's +/- 400GB and grows by 8GB each day) - I think it's about 2.5 billion rows at this point. I want to delete rows identified via column a, but unless I specify either b or c in the where clause, the query plan indicates that a sequential scan on public.values will be done.

i.e. this query:

-- ... After running `analyze` on the tables that are used

explain delete from public.values where a = 2

shows the query plan (with 60 unique a values):

Delete on "values"  (cost=0.00..62375313.80 rows=0 width=0)
  ->  Seq Scan on "values"  (cost=0.00..62375313.80 rows=48160325 width=6)
        Filter: (a = 2)
JIT:
  Functions: 3
  Options: Inlining true, Optimization true, Expressions true, Deforming true

This makes no sense to me - I don't understand why the unique constraint index isn't used (I would expect this query to delete 50 million rows).

If I update the query to specify b, then I can get the query plan to indicate that an index scan will be done:

explain delete from public.values where a = 2 and b = 1

Shows this query plan:

Delete on "values"  (cost=0.58..4845719.33 rows=0 width=0)
  ->  Index Scan using values_unique_cols on "values"  (cost=0.58..4845719.33 rows=1940861 width=6)
        Index Cond: ((a = 2) AND (b = 1))
JIT:
  Functions: 3
  Options: Inlining true, Optimization true, Expressions true, Deforming true

Then if I change the delete query b clause "where" condition to be a greater than condition:

explain delete from public.values where a = 2 and b >= 1

... Back to the sequential scan.

BUT Changing the clause again:

explain delete from public.values where a = 2 and b  >= 7

And the plan is better (b lower than 6 results in a seq scan - b is a value between 1 and 20):

Delete on "values"  (cost=0.58..65344628.48 rows=0 width=0)
  ->  Index Scan using values_unique_cols on "values"  (cost=0.58..65344628.48 rows=36595426 width=6)
        Index Cond: ((a = 2) AND (b >= 7))
JIT:
  Functions: 3
  Options: Inlining true, Optimization true, Expressions true, Deforming true

How can I force PostgreSQL to use an index for a particular operation, or otherwise delete a large number of rows quickly?

I could run the query multiple times from the application, each time with a new b value, but I would prefer to make a single database call.

== EDIT

Subsequently to posting this question, I created an index on a, and re-analyzed the table. But the planner still indicates a seq. scan.

5
  • 2
    For deleting 2% of a table, I would expect the seq scan to actually be faster. Are you sure it isn't?
    – jjanes
    Oct 31, 2022 at 14:16
  • 2
    Discussion of random_page_cost and indexes: dba.stackexchange.com/a/114868/234725
    – dwhitemv
    Oct 31, 2022 at 14:33
  • @jjanes I hadn't actually thought of that. I'll try it. My first attempt was actually to have a cascade delete on the a column. But that didn't work - I assumed that it was because the trigger was using a seq scan, which I thought was the problem. Would a cascade delete be slower than delete where id = 2?
    – Zach Smith
    Oct 31, 2022 at 19:14
  • Lowering the random_page_cost parameter results in the database choosing to use an index - and the query completes in a reasonable time. I have previously tried the seq. scan and it took much longer, but I can't remember if the table structure was the same (I think I had additional spatial indexes and columns). In any case, with the adjusted random_page_cost parameter the query is fast enough, and that is the answer I was looking for @dwhitemv. Thanks
    – Zach Smith
    Nov 3, 2022 at 8:08
  • I’d written a big essay on i/o costs as my first answer and deleted it since it wasn’t as big an effect in my environment, but that reinforces how situation-specific tuning is. Glad to hear it helped in your situation.
    – dwhitemv
    Nov 4, 2022 at 19:33

1 Answer 1

2

No, you cannot force PostgreSQL to use an index for a command. You have to make the index appetizing enough for the planner to choose it.

Based on your description of your table activity, I suspect your column statistics are inaccurate and that is throwing off plan choice. You can also influence the planner by changing the I/O cost values to move the threshold between index and sequential scans, but there lies dragons.

As for a cascaded delete, it will depend on how the planner builds the subquery. I think it would translate into something like DELETE FROM values WHERE a=2 AND d=2 which will break on whether the selectivity of d is better than a and thus if the values_d index is more useful than the unique index, if any index at all.

References:

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.