Posgresql single table of ten million data, paging query, can according to the time sequence control in three seconds?


Table has index: the create index door_event_index on door_event_info (event_time door_id, control_indexcode);

The following query
Select * from door_event_info

Where
Door_id in (' 330108000021600000 ac ', '330108000021600000' ab ', '330108000021600000 AD)
And
Event_time between '12:12:12' 2017-10-28 and '2019-11-13 12:12:12'
The order by event_time
Offset 0 limit 20;

CodePudding user response:

Discovery is paging conditions at the time of the count sequential scan of the table, slow down the speed of the whole, have bosses have a solution

CodePudding user response:

Where is the count?
A: what's the plan?
What is control_indexcode this field is not empty?

CodePudding user response:

refer to the second floor minsic78 response:

where the count?
A: what's the plan?
What is control_indexcode this field is not empty?

No compulsion is not empty, this field has a value of data in database, the execution plan is:

CodePudding user response:

Attempts to close the table scan, see if you can induce the optimizer to go to the index, and the performance is no problem,
If not, try to create such an index try: (event_time, door_id, 0), or the original index in the field to create a not null constraint,

CodePudding user response:

reference 4 floor minsic78 response:

attempts to close the table scan, see if you can induce the optimizer walk to the index, and the performance is no problem,
If not, try to create such an index try: (event_time, door_id, 0), or the original index field to create a not null constraint,

Wanted to think, the second approach in this case should be meaningless,

CodePudding user response:

The set enable_seqscan=off;

CodePudding user response:

Obviously, look at the query plan, query didn't leave the index, in other words the original joint index for you this query is invalid, and suggest to event_time, door_id separately built two indexes in time (space), should query plan could walk according to the index,

CodePudding user response:

If the original poster door_id and event_time is fixed in the where condition (from SQL to guess, the former can be fixed), you can try to create partial index to optimize a try,

CodePudding user response:

refer to the eighth floor minsic78 response:

if the original poster door_id and event_time is fixed in the where condition (from SQL to guess, the former can be fixed), you can try to create partial index to optimize a try,

Door_id is not fixed, change the scope of the relatively small, about ten thousand to twenty thousand, partial index should be how to build index, there is no relative to the size of the relationship between them, the door_id evenly distributed in the data

CodePudding user response:

guess I cough up reference 7 floor response:

obviously, look at the query plan, query didn't leave the index, in other words the original joint index for you this query is invalid, and suggest to event_time, door_id separately built two indexes in time (space), should be a query plan could walk according to the index,

Should not, time index hit, but door_id the may plan implement feel walk index than sequential scans to fast, so choose the sequential scan

CodePudding user response:

references 9/f, jiangnan leaf bamboo raft response:

Quote: refer to the eighth floor minsic78 response:

If the original poster door_id and event_time is fixed in the where condition (from SQL to guess, the former can be fixed), you can try to create partial index to optimize a try,

Door_id is not fixed, change the scope of the relatively small, about ten thousand to twenty thousand, partial index should be how to build index, there is no relative to the size of the relationship between them, the data of door_id evenly distributed

If there is no fixed conditions, then build partial index do not have what meaning,

references to the tenth floor jiangnan leaf bamboo raft response:

Quote: guess I cough up reference 7 floor response:

Obviously, look at the query plan, query didn't leave the index, in other words the original joint index for you this query is invalid, and suggest to event_time, door_id separately built two indexes in time (space), should be a query plan could walk according to the index,

Should not, time index hit, but door_id the may plan implement feel walk index than sequential scans to fast, so choose the sequential scan

You put the execution of the plan is not a sequential scan? Where there is hit event_id index?
Besides the above mentioned advice after closed sequential scans look at the execution plan, have a try? Very simple, can close session level: set enable_seqscan=off, and then obtain the SQL execution plan

CodePudding user response:

references to the tenth floor jiangnan leaf bamboo raft response:

Quote: guess I cough up reference 7 floor response:

Obviously, look at the query plan, query didn't leave the index, in other words the original joint index for you this query is invalid, and suggest to event_time, door_id separately built two indexes in time (space), should be a query plan could walk according to the index,

Should not, time index hit, but door_id the may plan implement feel walk index than sequential scans to fast, so choose the sequential scan

Time index hit?? Stick down time index hit a figure? See you hair figure, should be a full table scan, what index all missed it,,