如何加速SQLAlchemy查询？_sql

概述我有一张超过1000万行的表.大约有50列.该表存储传感器数据/参数.假设我需要查询全天或86,400秒的数据.完成此查询需要大约20秒或更长时间. 我在几个列上添加了单独的索引,例如recordTimestamp(捕获数据时存储),deviceId(传感器的标识),positionValid(GPS地理定位是否有效).然后我添加了一个包含所有三列的复合索引. 以下是我的查询： t1 = time 我有一张超过1000万行的表.大约有50列.该表存储传感器数据/参数.假设我需要查询全天或86,400秒的数据.完成此查询需要大约20秒或更长时间.

我在几个列上添加了单独的索引,例如recordTimestamp(捕获数据时存储),deviceid(传感器的标识),positionValID(GPS地理定位是否有效).然后我添加了一个包含所有三列的复合索引.

以下是我的查询：

t1 = time.time()conn = engine.connect()select_statement = select([Datatable]).where(and_(    Datatable.recordTimestamp >= start_date,Datatable.recordTimestamp <= end_date,Datatable.deviceid == device_ID,Datatable.positionValID != None,Datatable.recordTimestamp % query_interval == 0))lol_data = conn.execute(select_statement).fetchall()    conn.close() t2 = time.time()time_taken = t2 - t1print('Select: ' + time_taken)

以下是我的EXPLAIN ANALYZE声明：

EXPLAIN ANALYZE SELECT datatable.ID,datatable."createdAt",datatable."analoginput01",datatable."analoginput02",datatable."analoginput03",datatable."analoginput04",datatable."analoginput05",datatable."analoginput06",datatable."analoginput07",datatable."canEngineRpm",datatable."canEngineTemperature",datatable."canFuelConsumedliters",datatable."canFuelLevel",datatable."canVehicleMileage",datatable."deviceid",datatable."deviceTemperature",datatable."deviceInternalVoltage",datatable."deviceExternalVoltage",datatable."deviceAntennaCut",datatable."deviceEnum",datatable."deviceVehicleMileage",datatable."deviceSimsignal",datatable."deviceSimstatus",datatable."ibutton01",datatable."ibutton02",datatable."recordSequence",datatable."recordTimestamp",datatable."accelerationabsolute",datatable."accelerationBrake",datatable."accelerationBump",datatable."accelerationTurn",datatable."accelerationX",datatable."accelerationY",datatable."accelerationZ",datatable."positionAltitude",datatable."positionDirection",datatable."positionSatellites",datatable."positionSpeed",datatable."positionLatitude",datatable."positionLongitude",datatable."positionHdop",datatable."positionMovement",datatable."positionValID",datatable."positionEngine" FROM datatable WHERE datatable."recordTimestamp" >= 1519744521 AND datatable."recordTimestamp" <= 1519745181 AND datatable."deviceid" = '864495033990901' AND datatable."positionValID" IS NOT NulL AND datatable."recordTimestamp" % 1 = 0;

以下是SELECT的EXPLAIN ANALYZE的结果：

Index Scan using "ix_datatable_recordTimestamp" on datatable (cost=0.44..599.35 rows=5 wIDth=301) (actual time=0.070..10.487 rows=661 loops=1)Index Cond: (("recordTimestamp" >= 1519744521) AND ("recordTimestamp" <= 1519745181))Filter: (("positionValID" IS NOT NulL) AND (("deviceid")::text = '864495033990901'::text) AND (("recordTimestamp" % 1) = 0))Rows Removed by Filter: 6970Planning time: 0.347 msExecution time: 10.658 ms

以下是Python计算的时间结果：

Select:  47.98712515830994 JsON:  0.19731807708740234

以下是我的代码分析：

10302 function calls (10235 primitive calls) in 12.612 secondsOrdered by: cumulative timencalls  tottime  percall  cumtime  percall filename:lineno(function)    1    0.000    0.000   12.595   12.595 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py:882(execute)    1    0.000    0.000   12.595   12.595 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/sql/elements.py:267(_execute_on_connection)    1    0.000    0.000   12.595   12.595 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py:1016(_execute_clauseelement)    1    0.000    0.000   12.592   12.592 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/base.py:1111(_execute_context)    1    0.000    0.000   12.590   12.590 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/default.py:506(do_execute)    1   12.590   12.590   12.590   12.590 {method 'execute' of 'psycopg2.extensions.cursor' objects}    1    0.000    0.000    0.017    0.017 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/result.py:1113(fetchall)    1    0.000    0.000    0.017    0.017 /Users/afeezaziz/Projects/Bursa/backend/env/lib/python3.6/site-packages/sqlalchemy/engine/result.py:1080(_fetchall_impl)    1    0.008    0.008    0.017    0.017 {method 'fetchall' of 'psycopg2.extensions.cursor' objects}