0

Consider the following table:

|-----------------------------------------------------|
| raffle                                              |
|----|---------|----------|-----|---------------------|
| id | shuffle |  user_id | ... |           notify_at |
|----|---------|----------|-----|---------------------|
| 1  | 4D6G8Z1 |      542 | ... | 2019-12-01 14:00:00 |
| 2  | 64G264D |        6 | ... | 2019-12-28 14:00:00 |
| 3  | 4IPF93D |       58 | ... | 2020-01-01 14:00:00 |
| 4  | D25LF03 |       58 | ... | 2020-01-14 14:00:00 |
| 5  | G04LDWE |      684 | ... | 2020-03-02 13:00:00 |

In this table, most requests are not done to the id column, but to the user_id and notify_at, which is a 64-bit timestamp (no 2038 Bug):

SELECT * 
  FROM [raffle] 
 WHERE [user_id] = ? 
   AND [notify_at] = ?

The table grows by the minute, but that is not the problem, but rather, the records for the notify_at in the current month are most accessed than the rest. In 10.000.000 records, an index of the user_id and notify_at sums 160MB, which only 1% of these are heavily accessed.

Is there a way to optimize an index (or any other strategy) to make retrieval of the records for the current month snappier (as in, "trying to use the index instead of sweeping the whole table for records)?

Update 1: I'm asking this way because the table holds many notifications. This would grow large over time, and the SQL query would only take those in the current month:

SELECT * 
  FROM raffle 
 WHERE user_id = 542 
   AND notify_at > '2020-01-01 00:00:00' 
   AND notify_at < '2020-01-31 23:59:59'

As you can see, the index would also grow larger.

  • 1
    Which database product are you using? What does "snappier" mean here, how fast do you need it to be and how fast is it currently? Please also include the table and index DDL for people to reproduce. – LowlyDBA Jan 3 at 21:37
  • Hi, and welcome to the forum! Please always include your database server in the question tag list as many (most?) answers will either depend on this or be better in some way because of knowing it. It's only that @zsheep took the trouble to look at your profile and saw that you had previously asked a PostgreSQL question that he was able to provide a PostgreSQL suitable answer! – Vérace Jan 4 at 6:42
  • Thanks, totally forgot I posted this here. – DarkGhostHunter Jan 5 at 0:24
1

The index type you are looking for is Range Type

  • Can you elaborate on how this will help? – Lennart Jan 4 at 0:28
  • the OP had just did a question regarding Postgresql and I assumed this question is regarding the same db. the OP states the most recent based on user_id and date within a month. This fits perfect the Range Type with an index. The first paragraph of the linked page gives an almost identical use case – zsheep Jan 4 at 0:59
  • Link only answers are frowned upon here (link rot, easy-way-out approach). Perhaps you could expand on your answer a little - maybe just copy and post in the example from the linked documentation? This site aims to be a one-stop-shop for Q/A - having to follow links defeats that aim. In fact, I have followed the iink and it's not immediately obvious to me how range types will help the OP - could you demonstrate? It'll get you a +1! :-) I haven't used these types before and would be VERY interested in learning! – Vérace Jan 4 at 6:51
  • @Vérace sorry for taking so long to create the sample, doing family stuff, go get duck, chicken, dog , cat, split fire wood, and grocery store.. – zsheep Jan 5 at 3:07
  • I hear you brother - Christmas/New Year and all that! :-) But, you've now provided three answers to the one question - might I suggest that you consolidate your answers into a single one and delete the others? I'll upvote for your range type answer alone - nice one. Let me know when and what you've finally decided on how to answer the question! – Vérace Jan 5 at 10:39
0

the other possible solution requires modifying the table. This more simple approach using simple equality method and uses composite index

create table plrange ( id serial primary key,
                       user_id int,
                       notify_start date  default now()::date,
                       notify_end date default now()::date + 30);

 insert into plrange (user_id, notify_start, notify_end) 
 select (random()*100)::int, 
         now()::date - (random()*10)::int ,
         now()::date
    from generate_series(1,1000);


  create index on plrange using btree(user_id, notify_end );  

  explain select * from plrange where
  user_id =4 and notify_end > now()::date
0

Asked to give a sample of how to use RANGE type. I do not think it possible to implement this solution without adding/modifying column to the table, during writing this i thought of another solution that would not use Range Type but still requires modifying the table will add it as another answer

There are a few gotchas with range type with indexes and the special operators,

  1. the GIST index the preferred index as it has allot operators
  2. Btree and hash indexes only do = or != so not very useful
  3. GIST index does not support composite indexes.
  4. Range type uses different operators read the manual

Range type is a very useful tool to quickly find a value so here is 1 method to do that

create table plrange ( id serial primary key,
                  user_id int,
                  notify_range daterange);

insert into plrange (user_id, notify_range) 
  select (random()*100)::int, 
      daterange(now()::date - (random()*10)::int , 
            now()::date )
    from generate_series(1,1000);

create index on plrange using gist(notify_range); 

create view Last_30days as select * from plrange 
    where notify_range @> (now()::date -30)::date

select * from last_30days where user_id = 7

This creates a view that returns all the records that are in the last 30 days which can be filtered down more .

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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