主页 / user-205706

Nikola's questions

Martin Hope
Nikola
Asked: 2022-01-19 07:01:00 +0800 CST
  • 2

我无法在我的 PostgreSQL C 扩展代码中获取环境变量。

例如，此函数始终返回111：

#include "postgres.h"
#include "fmgr.h"
#include <stdlib.h>

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(myinc);
Datum
myinc(PG_FUNCTION_ARGS)
{
  int32 arg = PG_GETARG_INT32(0);
  char* envar = getenv("MYINC");
  if (envar) {
    PG_RETURN_INT32(arg + atoi(envar));
  } else {
    PG_RETURN_INT32(111);
  }
}

虽然此 C 程序按预期工作，但它会打印以下内容MYINC：

#include <stdio.h>
#include <stdlib.h>

int main()
{
  printf("MYINC: %s", getenv("MYINC"));
  return 0;
}
postgresql postgresql-extensions
Martin Hope
Nikola
Asked: 2020-04-04 15:02:45 +0800 CST
  • 1

有没有办法避免由于函数未被​​识别为索引的一部分而导致的Sort操作？该功能不会真正影响.GROUP BYDATE_TRUNCtstampixix

CREATE INDEX ix ON tbl(col, tstamp, num)

  SELECT col,
         DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC') AS trunc,
         SUM(num)
    FROM tbl
   WHERE col IN (?, ?, ...)
     AND tstamp >= ?
     AND tstamp <  ?
GROUP BY col, trunc

精度参数DATE_TRUNC是动态的，它也可以是'DAY'或'YEAR'……所以如果不为每个变体创建索引，我就无法索引该函数。

我得到的执行计划：

Finalize GroupAggregate
  Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
  ->  Gather Merge
        Workers Planned: 2
        Workers Launched: 2
        ->  Partial GroupAggregate
              Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
              ->  Sort
                    Sort Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
                    Sort Method: external merge  Disk: ...kB
                    -> Parallel Index Only Scan using ix on tbl
                         Index Cond: ((col = ANY (...)) AND (tstamp >= ...) AND (tstamp < ...))
                         Heap Fetches: 0

我想要的执行计划看起来像这样：

GroupAggregate
  Group Key: col, DATE_TRUNC('MONTH', tstamp AT TIME ZONE 'UTC')
  ->  Index Only Scan using ix on tbl
        Index Cond: ((col = ANY (...)) AND (tstamp >= ...) AND (tstamp < ...))
        Heap Fetches: 0

（如果我不使用，我会明白DATE_TRUNC）

postgresql performance