Postgres query optimization?

Question

PostgreSQL

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;;

возвращено записей: 7 (выполнено: 11,279 с; всего: 11,279 с)" */

/------ 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)

возвращено записей: 4 (выполнено: 9,360 с; всего: 9,375 с)" */

it's a total nightmare. Maybe it makes sense to sort already on the client, or what other considerations can there be?

Answer 1

des1roer : CREATE INDEX as far as I understand creates indexes for the most frequently accessed data. but if the data is dynamic then how to do it and will it help

des1roer : You misunderstand.
1) the index is needed to speed up the search and sorting
2) without "order by" the order of the tuples is not guaranteed at all.
even with "order by" if several tuples have the same values ​​in the sorted field, their order is not guaranteed.
index by (F_Date, F_TagName_ID) will significantly speed up the selection, I believe
3) In order of delirium: because you have a lot of records in the table - it makes sense to think about whether they should be stored in a relational database, or can be aggregated into lists, for example, by hours and put in some kind of monga? Well, or at least clean up the old records. Or transfer them to another table.

Answer 2

Start with CREATE INDEX