existing problem. There is a table divided into partitions. I want to make a sample using some value (specifically, the time when the shift began).

but apparently it is a request for a partition with a time each time the request is sampled (that is, the values ​​of starting.starting are retrieved for each partition)

is it possible to tell Postgres that these values ​​do not need to be selected each time (cached while the question is being run, or something)

SELECT tag.* FROM "SC_Tag"."T_TagData" tag, (SELECT "ID_TagData", "F_Date" AS starting, MAX ("F_ConvertedValue") FROM "SC_Tag"."T_TagData" WHERE "F_TagName_ID"=87 AND "F_Date" > TIMESTAMP'2016-02-4 7:50:00' AND "F_Date" < TIMESTAMP'2016-02-4 8:10:00' GROUP BY "F_Date", "ID_TagData" ORDER BY "F_Date" DESC LIMIT 1) starting, (SELECT "ID_TagData", "F_Date" AS ending, MAX ("F_ConvertedValue") FROM "SC_Tag"."T_TagData" WHERE "F_TagName_ID"=87 AND "F_Date" > TIMESTAMP'2016-02-4 19:50:00' AND "F_Date" < TIMESTAMP'2016-02-4 20:10:00' GROUP BY "F_Date", "ID_TagData" ORDER BY "F_Date" DESC LIMIT 1) ending WHERE (tag."F_Date" > starting.starting AND tag."F_Date" <= TIMESTAMP'2016-02-4 20:10:00') AND tag."F_TagName_ID"=87 ORDER BY tag."F_Date" desc

plan

https://vk.com/doc19953508_437262011

  • It is not clear what "every time" means. each subquery in from is executed once. Once again he doesn't have to do it anyway - Mike

1 answer 1

Try with the WITH statement:

 WITH starting AS ( SELECT "ID_TagData", "F_Date" AS starting, MAX ("F_ConvertedValue") FROM "SC_Tag"."T_TagData" WHERE "F_TagName_ID"=87 AND "F_Date" > TIMESTAMP'2016-02-4 7:50:00' AND "F_Date" < TIMESTAMP'2016-02-4 8:10:00' GROUP BY "F_Date", "ID_TagData" ORDER BY "F_Date" DESC LIMIT 1), ending AS ( SELECT "ID_TagData", "F_Date" AS ending, MAX ("F_ConvertedValue") FROM "SC_Tag"."T_TagData" WHERE "F_TagName_ID"=87 AND "F_Date" > TIMESTAMP'2016-02-4 19:50:00' AND "F_Date" < TIMESTAMP'2016-02-4 20:10:00' GROUP BY "F_Date", "ID_TagData" ORDER BY "F_Date" DESC LIMIT 1) SELECT tag.* FROM "SC_Tag"."T_TagData" tag, starting, ending WHERE (tag."F_Date" > starting.starting AND tag."F_Date" <= TIMESTAMP'2016-02-4 20:10:00') AND tag."F_TagName_ID"=87 ORDER BY tag."F_Date" desc