CODEWITHSUNDEEP
sqlpostgresql
vishalv2050
vishalv2050

Reputation: 883

Delete based on column values when it is part of a composite key

I have a table which has an id and a date. (id, date) make up the composite key for the table.

What I am trying to do is delete all entries older than a specific date.

delete from my_table where date < '2018-12-12'

The query plan explains that it will do a sequential scan for the date column.

I somehow want to make use of the index present since the number of distinct ids are very very small compared to total rows in the table.

How do I do it ? I have tried searching for it but to no avail

Upvotes: 0

Views: 67

Answers (2)

swayamraina
swayamraina

Reputation: 3158

In case your use-case involves data-archival on monthly basis or some time period, you can think of updating your DataBase table to use partitions.

Let's say you collect data on monthly basis and want to keep data for the last 5 months. It would be really efficient to create partition over the table based on month of the year.

This will,

  • optimise your READ queries (table scans will reduce to partition scans)
  • optimise your DELETE requests (just delete the complete partition)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You need an index on date for this query:

create index idx_mytable_date on mytable(date);

Alternatively, you can drop your existing index and add a new one with (date, id). date needs to be the first key for this query.

Upvotes: 0

Related Questions