PostgreSQL 9.3.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

/*------ 04.02.2015 8:40:12 --------*/ EXPLAIN ANALYZE SELECT EXTRACT(EPOCH FROM "F_Date"), "F_ConvertedValue", "F_TagName_ID" FROM "SC_Tag"."T_TagData" where "F_Date" > (localtimestamp - interval'1 hour') and "F_TagName_ID" in (73,72,39,64,76,75) ORDER BY "F_Date" asc;; /* Результат : "LOG: duration: 11276.442 ms statement: EXPLAIN ANALYZE SELECT EXTRACT(EPOCH FROM "F_Date"), "F_ConvertedValue", "F_TagName_ID" FROM "SC_Tag"."T_TagData" where "F_Date" > (localtimestamp - interval'1 hour') and "F_TagName_ID" in (73,72,39,64,76,75) ORDER BY "F_Date" asc;;

Records returned: 7 (completed: 11.279 seconds; total: 11.279 seconds) "* ​​/

 /*------ 04.02.2015 8:49:52 --------*/ EXPLAIN ANALYZE SELECT EXTRACT(EPOCH FROM "F_Date"), "F_ConvertedValue", "F_TagName_ID" FROM "SC_Tag"."T_TagData" where "F_Date" > (localtimestamp - interval'1 hour') and "F_TagName_ID" in (73,72,39,64,76,75) --ORDER BY "F_Date" asc;; /* Результат : "LOG: duration: 9359.324 ms statement: EXPLAIN ANALYZE SELECT EXTRACT(EPOCH FROM "F_Date"), "F_ConvertedValue", "F_TagName_ID" FROM "SC_Tag"."T_TagData" where "F_Date" > (localtimestamp - interval'1 hour') and "F_TagName_ID" in (73,72,39,64,76,75)

Records returned: 4 (completed: 9.360 seconds; total: 9.375 seconds) "* ​​/

alt text

alt text

This is generally a nightmare. Maybe it makes sense to sort on the client already, maybe, what other considerations will there be?

  • 2
    Do you have indexes on F_Date and F_TagName_ID ? It seems that Seq Scan on a table of several tens of millions is not a good idea. - a_gura

1 answer 1

CREATE INDEX