Skip to content
Advertisement

Multi-column Index on a filter & sort by query

I am executing an query of type:

Query:

select * 
from entity 
where last_updation_date between <START-VALUE-1> and <END-VALUE>  
order by entity_id limit <XYZ> offset <ABC>;

Table: entity also has an index (Btree) on it as such –

Index:

create index i_last_updation_date_idx 
   on entity using btree  (last_updation_date, entity_id)

But it seems that this type of index is of no use on the above query. Can anyone suggest what kind of index would better serve this type of use-case?

Some Additional Information –

The Table is quite huge, contains around 1 TB data. The data type of last_updation_date is timestamp with precision set to 29 & scale set to 6.

The table has around 1,81,79,77,192 rows. Regarding entity_id – it the primary key generated by a sequence generator. last_updation_date is pretty random (gets updated whenever a client updates the entity). The value of last_updation_date ranges from timestamp 16,01,51,39,96,400 ms (i.e. 1st Oct 2020) to 16,04,97,00,00,000 ms (i.e. 1st Nov 2021)

Advertisement

Answer

I was able solve this problem by modifying my query (order by clause) as such –

select * 
from entity 
where last_updation_date between <START-VALUE-1> and <END-VALUE>  
order by last_updation_date, entity_id limit <XYZ> offset <ABC>;

This query was making use of the index i mentioned.

It was not a strict requirement for me to order the results specifically by entity_id. All i needed was an approach to fetch results in a paginated manner using LIMIT & OFFSET. And simply using last_updation_date for pagination would not work because they are not guaranteed to be unique always (there can be duplicates). Hence, i used both last_updation_date & entity_id.

5 People found this is helpful
Advertisement