explain (analyze, buffers) select * from door_event_info d 
Where 
D.d oor_id (select in a. d. oor_id from door_device_info a) and 
D.e vent_time & gt; 'the 2017-11-12 12:12:12 and d.e vent_time & lt; 'the 2019-12-12 12:12:12' 

The order by d.e vent_time offset 1295980 limit 20; 

1. You can't use site to optimize, because with the condition after site of discontinuity,

2. Door_id value around two thousand, uniform distribution of the five million data

The current conditions of five million data, the execution plan is as follows:

 Limit (cost=246144.59.. 246144.60 rows=1 width=6) (actual time=15787.731.. 15787.744 rows=20 loops=1) 
Buffers: Shared hit=26330 read=111413, temp read=3264 written=11441 
-> Sort (cost=245854.63.. 246144.59 rows=115984 width=6) (actual time=14965.301. 15733.105 rows=1296000 loops=1) 
The Sort Key: d.i d 
The Sort Method: external merge Disk: 91376 KB 
Buffers: Shared hit=26330 read=111413, temp read=3264 written=11441 
-> Hash Semi Join (cost=43.97.. 236098.31 rows=115984 width=6) (actual time=0.987. 5382.321 rows=5502007 loops=1) 
Hash Cond: ((d.d oor_id) : : text=(a. d. oor_id) : : text) 
Buffers: Shared hit=26330 read=111413 
-> Seq Scan on door_event_info d (cost=0.00.. 220320.00 rows=5502484 width=28) (actual time=0.015. 3632.381 rows=5502007 loops=1) 
The Filter: ((event_time & gt; 'the 2017-11-12 12:12:12: : timestamp without time zone) AND (event_time & lt; 'the 2019-12-12 12:12:12: : timestamp without time zone)) 
Rows Removed by the Filter: 5993 
Buffers: Shared hit=26287 read=111413 
-> Hash (cost=43.43.. 43.43 rows=43 width=516) (actual time=0.957. 0.957 rows=2040 loops=1) 
Buckets: 2048 (1024) originally Batches: 1 (1) originally the Memory Usage: 126 KB 
Buffers: Shared hit=43 
-> Seq Scan on door_device_info a (cost=0.00.. 43.43 rows=43 width=516) (actual time=0.009. 0.458 rows=2040 loops=1) 
Buffers: Shared hit=43 
Planning time: 0.709 ms 
Execution time: 15808.060 ms 

CodePudding user response:

I don't know what to site?

There is an index on d.e vent_time field? Built without a look at, whether go to d table index, and then use a nested loop to complete the whole SQL, but no matter how to optimize, paging are generally more slow climb back

CodePudding user response:

Either a self-built site, or using a cursor, other should do not have what method to optimize

CodePudding user response:

Need 20 lines of seemingly simple truth, but you have to filter to the previous 1295980 to find the 20 lines, you want with offset more and more, of course, will be more and more slow,
In fact, this is the problem that design idea to turn the page on the database side, in addition to every time overhead is very large, and the hidden trouble of the result set is not stable, if between two query, data update, it didn't get any correct result sets,

Can consider one-time data output to the forefront of sqlite,
If must be done on the database side, can consider to build a temporary table,