0

I have kinda Truck table in my Postgresql DB. I write a new data every minute. And I want to get latest element of each group by serial. But problem is my database is so huge and making query takes long time. Now I have 470000 row data. I tried make query: 

rec_ = Trucks.objects.filter(serial='tsr1801').distinct('serial').order_by('serial', '-id')

It is responding so slow. And I tried other methods shown as below, but these queries are also working slow.

1) https://stackoverflow.com/a/19930802/7456750

Mine: Trucks.objects.annotate(max_id=Max('id')).filter(id=F('max_id'))

2) https://stackoverflow.com/a/17887296/7456750

Mine: Trucks.objects.values('serial', 'payload', 'datetime').annotate(id=Max('id'))

Is there any way to get latest element of each group, which works fast?

+-----+-------------+------------+---------------------+
|  Id |   Serial    |  Payload   |     Datetime        |
+-----+-------------+------------+---------------------+
|  1  |   tsr1801   |   24432    | 2018-11-01 12:00:00 |
+-----+-------------+------------+---------------------+
|  2  |   tsr1802   |   20234    | 2018-11-01 12:01:00 |
+-----+-------------+------------+---------------------+
|  3  |   tsr1802   |   21234    | 2018-11-01 12:01:00 |
+-----+-------------+------------+---------------------+
|  4  |   tsr1801   |   24332    | 2018-11-01 12:02:00 |
+-----+-------------+------------+---------------------+
|  5  |   tsr1801   |   21532    | 2018-11-01 12:03:00 |
+-----+-------------+------------+---------------------+
|  6  |   tsr1802   |   19234    | 2018-11-01 12:02:00 |
+-----+-------------+------------+---------------------+
|  7  |   tsr1801   |   18040    | 2018-11-01 12:04:00 |
+-----+-------------+------------+---------------------+
|  9  |   tsr1801   |   27452    | 2018-11-01 12:05:00 |
+-----+-------------+------------+---------------------+
Bryan Woo
  • 189
  • 1
  • 4
  • 13

1 Answers1

0

I think you can use latest here. For example:

 Trucks.objects.filter(serial='tsr1801').latest('Datetime')  # as per table

Update

The solutions you have followed are already very good ones. Even if you use the following query (like the one you are using):

 Truck.objects.distinct('serial').order_by('serial', '-Datetime')

Should be fast enough.

So instead of changing the query, you should look elsewhere. Like update the indexes of your DB, use cache to reduce database hits etc

ruddra
  • 44,912
  • 7
  • 64
  • 88
  • I would like to get a latest element of each group by serial. But your query looks that make query one by one for each serial group. Do you think that it can work fast? Does not it make DB busy ? – Bryan Woo Nov 02 '18 at 04:15