从postgresql中的表中获取最大、最小时间戳值

Question

编辑:查看评论中的建议。

所讨论的表(gas_station_information_history)大约是5GB。

启动服务器、运行工作查询、然后运行MAX、MIN查询的日志文件如下：

2020-02-03 12:39:54.931 CET [18022] LOG:  database system was shut down at 2020-02-03 12:38:55 CET
2020-02-03 12:39:54.936 CET [18020] LOG:  database system is ready to accept connections
2020-02-03 12:40:26.615 CET [18020] LOG:  background worker "parallel worker" (PID 18040) was terminated by sig$
2020-02-03 12:40:26.615 CET [18020] DETAIL:  Failed process was running: SELECT MAX(date), MIN(date)
        FROM gas_station_information_history;
2020-02-03 12:40:26.615 CET [18020] LOG:  terminating any other active server processes
2020-02-03 12:40:26.615 CET [18038] WARNING:  terminating connection because of crash of another server process
2020-02-03 12:40:26.615 CET [18038] DETAIL:  The postmaster has commanded this server process to roll back the $
2020-02-03 12:40:26.615 CET [18038] HINT:  In a moment you should be able to reconnect to the database and repe$
2020-02-03 12:40:26.615 CET [18031] WARNING:  terminating connection because of crash of another server process
2020-02-03 12:40:26.615 CET [18031] DETAIL:  The postmaster has commanded this server process to roll back the $
2020-02-03 12:40:26.615 CET [18031] HINT:  In a moment you should be able to reconnect to the database and repe$
2020-02-03 12:40:26.616 CET [18027] WARNING:  terminating connection because of crash of another server process
2020-02-03 12:40:26.616 CET [18027] DETAIL:  The postmaster has commanded this server process to roll back the $
2020-02-03 12:40:26.616 CET [18027] HINT:  In a moment you should be able to reconnect to the database and repe$
2020-02-03 12:40:26.619 CET [18020] LOG:  all server processes terminated; reinitializing
2020-02-03 12:40:26.685 CET [18041] LOG:  database system was interrupted; last known up at 2020-02-03 12:39:54$
2020-02-03 12:40:26.748 CET [18041] LOG:  database system was not properly shut down; automatic recovery in pro$
2020-02-03 12:40:26.751 CET [18041] LOG:  redo starts at 3/F92C228
2020-02-03 12:40:26.751 CET [18041] LOG:  invalid record length at 3/F92C260: wanted 24, got 0
2020-02-03 12:40:26.751 CET [18041] LOG:  redo done at 3/F92C228
2020-02-03 12:40:26.784 CET [18020] LOG:  database system is ready to accept connections
2020-02-03 12:40:28.959 CET [18020] LOG:  background worker "parallel worker" (PID 18053) was terminated by sig$
2020-02-03 12:40:28.959 CET [18020] DETAIL:  Failed process was running: SELECT MAX(date), MIN(date)
        FROM gas_station_information_history;
2020-02-03 12:40:28.959 CET [18020] LOG:  terminating any other active server processes
2020-02-03 12:40:28.959 CET [18051] WARNING:  terminating connection because of crash of another server process
2020-02-03 12:40:28.959 CET [18051] DETAIL:  The postmaster has commanded this server process to roll back the $
2020-02-03 12:40:28.959 CET [18051] HINT:  In a moment you should be able to reconnect to the database and repe$
2020-02-03 12:40:28.959 CET [18049] WARNING:  terminating connection because of crash of another server process
2020-02-03 12:40:28.959 CET [18049] DETAIL:  The postmaster has commanded this server process to roll back the $
2020-02-03 12:40:28.959 CET [18049] HINT:  In a moment you should be able to reconnect to the database and repe$
2020-02-03 12:40:28.960 CET [18045] WARNING:  terminating connection because of crash of another server process
2020-02-03 12:40:28.960 CET [18045] DETAIL:  The postmaster has commanded this server process to roll back the $
2020-02-03 12:40:28.960 CET [18045] HINT:  In a moment you should be able to reconnect to the database and repe$
2020-02-03 12:40:28.964 CET [18020] LOG:  all server processes terminated; reinitializing
2020-02-03 12:40:29.038 CET [18054] LOG:  database system was interrupted; last known up at 2020-02-03 12:40:26$
2020-02-03 12:40:29.092 CET [18057] FATAL:  the database system is in recovery mode
2020-02-03 12:40:29.114 CET [18058] FATAL:  the database system is in recovery mode
2020-02-03 12:40:29.117 CET [18054] LOG:  database system was not properly shut down; automatic recovery in pro$
2020-02-03 12:40:29.121 CET [18054] LOG:  invalid record length at 3/F92C2D8: wanted 24, got 0
2020-02-03 12:40:29.121 CET [18054] LOG:  redo is not required
2020-02-03 12:40:29.154 CET [18020] LOG:  database system is ready to accept connections
2020-02-03 12:40:33.868 CET [18020] LOG:  received fast shutdown request
2020-02-03 12:40:33.869 CET [18020] LOG:  aborting any active transactions
2020-02-03 12:40:33.870 CET [18066] FATAL:  terminating connection due to administrator command
2020-02-03 12:40:33.871 CET [18020] LOG:  background worker "logical replication launcher" (PID 18064) exited w$
2020-02-03 12:40:33.873 CET [18059] LOG:  shutting down
2020-02-03 12:40:33.916 CET [18020] LOG:  database system is shut down

我在background worker "parallel worker" was terminated by sig$网上找不到任何东西。

在查询上运行EXPLAIN将提供：

Finalize Aggregate  (cost=1034772.50..1034772.51 rows=1 width=16)
  ->  Gather  (cost=1034772.28..1034772.49 rows=2 width=16)
        Workers Planned: 2
        ->  Partial Aggregate  (cost=1033772.28..1033772.29 rows=1 width=16)
              ->  Parallel Seq Scan on gas_station_information_history  (cost=0.00..895677.85 rows=27618885 width=8)
JIT:
  Functions: 5
  Options: Inlining true, Optimization true, Expressions true, Deforming true

如何从postgresql中的表中获取带有时区的第一次也是最后一次记录的时间戳？时间戳列名为“日期”。

我试过了

SELECT MAX(date), MIN(date)
FROM tablename;

它切断了我的联系：“这个套接字已经被另一方终止了”。我也试过

SELECT *
FROM table
ORDER BY date
LIMIT 10;

我查看了max、min和时间戳资源，例如

SELECT MAX (date) AS "Max Date" 
FROM orders;

这会产生同样的断开错误。

如何进行这个简单的查询？谢谢!

Answer 1

我通过在date列上添加一个索引来解决这个问题。谢谢你的帮忙!