我试图了解为什么以下查询对特定用户执行这么长时间（大约 5 分钟），但对于涉及相同行数的其他用户，它需要 2 / 3 秒

有一个主表“长度”与 FK 到“会话”表，长度表在 session_id 和 stroke_type 列上有一个 lengths_idx 索引，通常在这里使用

我可以看到它没有为该特定用户使用“长度”表上的 lengths_idx 索引，这可能是该表的索引/统计信息有问题吗？

在 Amazon RDS 上运行的 Postgresql 9.6

SELECT x.sess_id, round(count(x.*) * x.size,2)
FROM ( 
    SELECT l.session_id as sess_id, 
            l.stroke_type, 
            s.pool_size as size,row_number() OVER (ORDER BY l.session_id, l.stroke_type, l.length_number) - l.length_number AS grp 
    FROM lengths as l, user_sessions as us, sessions as s 
    WHERE l.session_id = us.session_id 
    AND us.user_id = 1234 
    AND l.session_id = s.session_id     
    AND CAST(l.stroke_type as text) = CAST('free' as text) 
) x 
GROUP BY x.sess_id, x.stroke_type, grp, x.size 
ORDER BY count(x.*) DESC LIMIT 1

快速执行的查询解释（索引扫描）：

Limit  (cost=5341076.22..5341076.23 rows=1 width=61) (actual time=2817.451..2817.452 rows=1 loops=1)
  ->  Sort  (cost=5341076.22..5341077.08 rows=342 width=61) (actual time=2817.449..2817.449 rows=1 loops=1)
        Sort Key: (count(x.*)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  GroupAggregate  (cost=5341062.54..5341074.51 rows=342 width=61) (actual time=2776.621..2817.029 rows=1136 loops=1)
              Group Key: x.sess_id, x.stroke_type, x.grp, x.size
              ->  Sort  (cost=5341062.54..5341063.40 rows=342 width=66) (actual time=2776.573..2793.144 rows=57154 loops=1)
                    Sort Key: x.sess_id, x.stroke_type, x.grp, x.size
                    Sort Method: external sort  Disk: 4248kB
                    ->  Subquery Scan on x  (cost=5341036.18..5341048.15 rows=342 width=66) (actual time=2627.123..2710.884 rows=57154 loops=1)
                          ->  WindowAgg  (cost=5341036.18..5341044.73 rows=342 width=25) (actual time=2627.108..2679.127 rows=57154 loops=1)
                                ->  Sort  (cost=5341036.18..5341037.03 rows=342 width=17) (actual time=2627.091..2644.249 rows=57154 loops=1)
                                      Sort Key: l.session_id, l.stroke_type, l.length_number
                                      Sort Method: external sort  Disk: 1512kB
                                      ->  Nested Loop  (cost=1.43..5341021.79 rows=342 width=17) (actual time=3.403..2571.724 rows=57154 loops=1)
                                            Join Filter: (us.session_id = l.session_id)
                                            ->  Nested Loop  (cost=0.86..13212.89 rows=1367 width=13) (actual time=1.854..494.049 rows=1079 loops=1)
                                                  ->  Index Only Scan using user_sessions_pkey on user_sessions us  (cost=0.43..1911.49 rows=1367 width=4) (actual time=0.947..86.638 rows=1079 loops=1)
                                                        Index Cond: (user_id = 2055)
                                                        Heap Fetches: 290
                                                  ->  Index Scan using sessions_pkey on sessions s  (cost=0.43..8.26 rows=1 width=9) (actual time=0.374..0.376 rows=1 loops=1079)
                                                        Index Cond: (session_id = us.session_id)
                                            ->  Index Scan using lengths_idx on lengths l  (cost=0.57..3897.30 rows=12 width=12) (actual time=1.208..1.899 rows=53 loops=1079)
                                                  Index Cond: (session_id = s.session_id)
                                                  Filter: ((stroke_type)::text = 'free'::text)
                                                  Rows Removed by Filter: 0
Planning time: 3.090 ms
Execution time: 2819.171 ms

慢速执行的查询解释（SEQ SCAN）：

Limit  (cost=5759727.25..5759727.25 rows=1 width=61) (actual time=315354.229..315354.231 rows=1 loops=1)
  ->  Sort  (cost=5759727.25..5759728.36 rows=444 width=61) (actual time=315354.228..315354.228 rows=1 loops=1)
        Sort Key: (count(x.*)) DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  GroupAggregate  (cost=5759709.49..5759725.03 rows=444 width=61) (actual time=315304.470..315353.719 rows=1094 loops=1)
              Group Key: x.sess_id, x.stroke_type, x.grp, x.size
              ->  Sort  (cost=5759709.49..5759710.60 rows=444 width=66) (actual time=315304.374..315324.696 rows=54090 loops=1)
                    Sort Key: x.sess_id, x.stroke_type, x.grp, x.size
                    Sort Method: external sort  Disk: 4016kB
                    ->  Subquery Scan on x  (cost=5759674.42..5759689.96 rows=444 width=66) (actual time=315123.778..315226.785 rows=54090 loops=1)
                          ->  WindowAgg  (cost=5759674.42..5759685.52 rows=444 width=25) (actual time=315123.762..315189.061 rows=54090 loops=1)
                                ->  Sort  (cost=5759674.42..5759675.53 rows=444 width=17) (actual time=315123.744..315147.132 rows=54090 loops=1)
                                      Sort Key: l.session_id, l.stroke_type, l.length_number
                                      Sort Method: external sort  Disk: 1432kB
                                      ->  Nested Loop  (cost=2484.15..5759654.90 rows=444 width=17) (actual time=251.322..315047.897 rows=54090 loops=1)
                                            ->  Hash Join  (cost=2483.72..5756479.95 rows=444 width=16) (actual time=250.846..312922.269 rows=54090 loops=1)
                                                  Hash Cond: (l.session_id = us.session_id)
                                                  ->  Seq Scan on lengths l  (cost=0.00..5750146.58 rows=1025390 width=12) (actual time=0.458..282569.670 rows=101216939 loops=1)
                                                        Filter: ((stroke_type)::text = 'free'::text)
                                                        Rows Removed by Filter: 103123974
                                                  ->  Hash  (cost=2461.52..2461.52 rows=1776 width=4) (actual time=118.626..118.626 rows=1059 loops=1)
                                                        Buckets: 2048  Batches: 1  Memory Usage: 54kB
                                                        ->  Index Only Scan using user_sessions_pkey on user_sessions us  (cost=0.43..2461.52 rows=1776 width=4) (actual time=0.963..118.151 rows=1059 loops=1)
                                                              Index Cond: (user_id = 43266)
                                                              Heap Fetches: 446
                                            ->  Index Scan using sessions_pkey on sessions s  (cost=0.43..7.14 rows=1 width=9) (actual time=0.037..0.038 rows=1 loops=54090)
                                                  Index Cond: (session_id = l.session_id)
Planning time: 0.572 ms
Execution time: 315356.321 ms

我正在使用 postgres 窗口函数来获取参加比赛的用户列表以及他们基于许多列的相应排名，到目前为止一切都很好....现在我需要根据“年龄组”获得排名许多预定义的类别（例如 0-15 岁、15 岁以上等），其中年龄是根据时间戳列“dob”计算的。

我很确定我可以使用 CASE 语句执行此操作，但我无法正确构建查询，我已经编写了下面的查询，该查询在单独的 FROM 子查询中定义了“年龄组”，因此我可以在 WINDOW 定义中引用它，但这不起作用，因为两个 FROM 是独立的

SELECT 
cu.* as compUser, 
cus.time_in_seconds as timeInSeconds,
rank() OVER allTimes as overallRank,
u.gender as gender,
rank() OVER genderTimes as genderRank,
ageGroup as ageGroup,
rank() OVER ageGroupTimes as ageGroupRank,
FROM 
competition_users cu, 
(SELECT CASE WHEN usr.dob>'2000-01-01' AND usr.dob<now() THEN '0-15' ELSE '15+' END FROM users usr WHERE usr.user_id = cu.user_id) ageGroup
LEFT JOIN users u ON cu.user_id = u.user_id
FULL JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id 
WHERE cu.left_competition = false 
AND cu.competition_id = :compId
WINDOW 
allTimes AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC),
genderTimes AS (PARTITION BY u.gender ORDER BY cus.time_in_seconds ASC),
ageGroupTimes AS (PARTITION BY ageGroup ORDER BY cus.time_in_seconds ASC)

（上面产生：错误：对表“cu”的 FROM 子句条目的引用无效...提示：表“cu”有一个条目，但不能从查询的这一部分引用。）

有人可以指出我正确的方向吗？（上面的示例查询是简化的，有很多年龄类别）

我有一些表格可以跟踪运动员在赛道上的时间：

竞赛、竞赛用户和竞赛用户会话

我正在构建一个查询，该查询为每个提供的“competition_user”提取当前排名和竞争对手总数，我可以得到排名，但我的计数（totalUsers）并没有计算比赛中的所有竞争对手，它似乎只是向上计数给提供的用户（例如，给出与排名相同的值）

SELECT compUserId, rank, totalUsers 
    FROM (
        SELECT cu.competition_user_id as compUserId, cu.user_id as userId,  
    count(*) OVER w as totalUsers, rank() OVER w as rank 
        FROM competition_users cu 
        LEFT JOIN current_competition_sessions ccs ON cu.competition_user_id = ccs.competition_user_id 
        LEFT JOIN competition_user_sessions cus ON cus.competition_user_session_id = ccs.competition_user_session_id 
        WHERE cu.left_competition = false 
        AND cu.competition_id in (:compIds)
        WINDOW w AS (PARTITION BY cu.competition_id ORDER BY cus.time_in_seconds ASC) 
    ) as sub 
WHERE compUserId in (:compUserIds)

我的理解是，默认框架是整个窗口，而这似乎是从框架开始计数到当前行？

我正在尝试将 postgresql 数据库从 EC2 实例导入到同一子网上的 RDS，到目前为止，我正在执行以下操作...

1) 转储 EC2 数据库：

pg_dump --host localhost --port 5432 -Fc --encoding='UTF8' -U postgres ProcessorDB > /home/jboss/proc_dump_jan15.dump

2) 从 EC2 恢复：

psql -f /home/jboss/proc_dump_jan15.dump --host=blahrds.11938475.eu-west-1.rds.amazonaws.com --port=5432 --username=postgres --password --dbname=ProcessorDB

我能够连接到远程数据库，但出现以下错误：

psql:/home/jboss/proc_dump_jan15.dump:1: ERROR:  syntax error at or near "PGDMP"
LINE 1: PGDMP
             REVOKE ALL ON SCHEMA public FROM postgres;
        ^
GRANT
GRANT
psql:/home/jboss/proc_dump_jan15.dump:5: ERROR:  syntax error at or near ""
LINE 1:     'en'
        ^
psql:/home/jboss/proc_dump_jan15.dump:14: ERROR:  syntax error at or near ""
LINE 1:     'free',
        ^
psql:/home/jboss/proc_dump_jan15.dump:21: ERROR:  syntax error at or near ""
LINE 1:     'windows',

这继续，然后更改为：

psql:/home/jboss/proc_dump_jan15.dump:316: ERROR:  invalid byte sequence for encoding "UTF8": 0xc2 0x40
psql:/home/jboss/proc_dump_jan15.dump:316: invalid command \?0?4
psql:/home/jboss/proc_dump_jan15.dump:317: invalid command \?????M?
psql:/home/jboss/proc_dump_jan15.dump:318: invalid command \)??LF,?C?{<??y??ݚ???Ɲ???0??{?dV
                                                                                           ?
psql:/home/jboss/proc_dump_jan15.dump:321: ERROR:  invalid byte sequence for encoding "UTF8": 0xbd
psql:/home/jboss/proc_dump_jan15.dump:331: invalid command \?`?v?ʺ??;6????6??N??.????c?~???&?G(??
psql:/home/jboss/proc_dump_jan15.dump:335: invalid command \@(??n']??sIZ??1VZ?º???????j?+??λ?=L?E??ܡ|c?0t
psql:/home/jboss/proc_dump_jan15.dump:338: invalid command \?A???N??/hL???Q??G??r
psql:/home/jboss/proc_dump_jan15.dump:354: invalid command \??I?$?R?o??¬???NJ?-F'??x?0z??z??hŗc?
psql:/home/jboss/proc_dump_jan15.dump:357: invalid command \x?r?Y??gMb~?墶6wб??m'aem?o????]X?s?????}??u?&?;-xY8?1)?}c??-@??h?K???IA?֒qv??Hs?g??J?DoD?Y?m
psql:/home/jboss/proc_dump_jan15.dump:361: invalid command \??W?]U?22?U-??N?
psql:/home/jboss/proc_dump_jan15.dump:362: invalid command \4??im?@??ښ?S?j???ꨫ?d܊פJ?cu??l!?ް̳u?5+^O??>=?k+]???԰A??5????~??N???0??:=??ՙM?3zm
psql:/home/jboss/proc_dump_jan15.dump:372: invalid command \HF??w?
psql:/home/jboss/proc_dump_jan15.dump:378: invalid command \g?#?s?t?I,???T?}5???

为简洁起见缩短...

我发现了“用于编码“UTF8”的无效字节序列”并将 --encoding 属性添加到转储命令中，但这没有任何效果

有谁知道可能是什么问题？我错过了转储选项吗？AWS 文档在这里有点含糊

NB。EC2 数据库是 PG 9.0，RDS 数据库是 PG 9.3.5，但我认为这没问题，因为转储/恢复将处理迁移？

我有一些包含网站和访问者的表格，我正在尝试编写一个查询，为每个网站生成“前 10 个”，这些表格看起来像这样......

websites
-------
website_id (pk)
url
description

visitors
-------
visitor_id (pk)
ip_address

website_visits
--------
visit_id (pk)
website_id (fk)
visitor_id (fk)
visit_date
visit_duration

我知道我可以使用两个 GROUP BY 来生成一个列表，该列表使用下面的查询按网站排序...

SELECT w.website_id, v.visitor_id, count(wv.visit_id)
FROM website_visits ws, websites w, visitors v
WHERE wv.website_id = w.website_id and wv.visitor_id = v.visitor_id
GROUP BY w.website_id, v.visitor_id
ORDER BY w.website_id ASC, count(wv.visit_id) DESC

但我想通过 a) 只显示每个站点的前 10 个，以及 b) 将每个前 10 个结果分组到一个数组中来改进这一点，这样结果看起来像这样：

website_id    top_ten
432           [{visitor_id:354,visits:958},{visitor_id:987,visits:971}...etc]
325           [{visitor_id:456,visits:955},{visitor_id:1056,visits:465}...etc]

（我在这里使用了 JSON 但它可以是任何格式）

我已经尝试在子查询中使用 SELECT ARRAY(...) 函数，但我似乎无法让它工作，我不确定这是否是正确的方法？

有人可以建议吗？谢谢

顺便使用PostgreSQL 9.0

这是一个基于我之前问题的解决方案的新问题

我有以下表格：

用户

int (pk) user_id
date last_visit
int (fk) local_site

网站

int (pk) site_id

网站统计

int (fk) site_id
int total_users
int monthly_users
int weekly_users

我正在使用这个查询来更新 user_stats 表

UPDATE site_stats
SET
    total_users = totalUsers,
    monthly_users = monthlyUsers,
    weekly_users = weeklyUsers
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
        count(CASE WHEN u.last_visit >= now()::DATE - 30 THEN 1 END) AS monthlyUsers, 
        count(CASE WHEN u.last_visit >= now()::DATE - 7 THEN 1 END) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site ;

这工作正常，除了如果在时间范围内有 0 个用户链接到站点，则不会发生更新，因此表 col 将保留以前的值而不是显示 0。

添加条件以使任何具有 0 个链接用户的网站都更新为 0 的最佳方法是什么？

我的第一个猜测是CASE在WHERE子句中使用 a ？

我有以下更新查询来填充存储用户统计信息的表

用户：

int (pk) user_id

上次访问日期

int (fk) local_site

网站：

整数 (pk) 站点 ID

网站统计：

int (fk) site_id

int total_users

int monthly_users

国际每周用户

要填充“total_users”列，我使用以下查询...

UPDATE site_stats
SET
    total_users = totalUsers,
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site

上面的查询工作正常，我现在想扩展它以更新 monthly_users 和 weekly_users

是否可以在同一个查询中执行？

我最初考虑使用窗口函数，但在阅读文档后我不太确定它是否可行，例如。

UPDATE site_stats
SET
    total_users = totalUsers,
    monthly_users = monthlyUsers,
    weekly_users = weeklyUsers
FROM
(
    SELECT
        u.local_site AS site,
        count (u.*) AS totalUsers,
        count(u.*) OVER ( ** where u.last_visit > now()::DATE - 30 ** ) AS monthlyUsers,
        count(u.*) OVER ( ** where u.last_visit > now()::DATE - 7 ** ) AS weeklyUsers
    FROM users u
    GROUP BY u.local_site
) AS subquery
WHERE site_stats.site_id = subquery.site

或者子查询会更合适吗？

我有一个关于数据库设计的问题，我正在使用 Hibernate for ORM 使用 PostgreSQL，我继承的设计包括下表

用户

user_id (pk) user_name user_dob ...

会话

session_id (pk) session_date session_duration ...

用户会话

user_id (fk) session_id (fk)

您可以猜到一个用户可以有多个会话，使用连接表的原因（我相信）是因为可以在用户存在之前添加一个会话（并且它在用户注册后链接到用户），从而避免了可为空会话表中的字段...很好。

但我最近一直在考虑创建一些多列索引以加快某些查询的速度，并且意识到我不能将用户包含在该索引中，因为该列位于不同的表中。

由于我对数据库设计还很陌生，所以我想知道上面的设计是否正确？还是我实际上最好在会话表中使用可为空的 FK 以允许我索引用户关系？

首先很抱歉，如果这是重复的，我对 SQL 还很陌生，所以我不确定在我的搜索中使用什么正确的术语

所以我有一个记录赛车的数据库，具有以下简化模式

race_table
==========
race_id PK
race_date timestamp
average_speed Decimal
max_speed Decimal

drivers
=======
driver_id PK
driver_name text
date_started timestamp

driver_races
============
driver_id FK
race_id FK

如果每个车手在 2/3 年内有大约 1000 场比赛

例如，我将如何查询给定日期范围内平均比赛速度的总体百分比变化（正负）

前 6 个月的百分比变化

Joe Smith - 5% increase
Andy James - 4% increase

前 12 个月的百分比变化

Joe Smith - 8% increase
Lewis May - 6% increase

更新：有关百分比变化的更多详细信息

百分比变化可以使用线性回归计算，（最小二乘拟合将是合适的），平均变化实际上是最佳拟合线上的 y 差，其中每个点都是一场比赛，x 是race_date 和 y 是那场比赛的平均速度。

Postgres 的 regr_slope 将给出与 %change 相同的线的梯度

SELECT regr_slope(r.average_speed, EXTRACT(EPOCH FROM r.race_date)) as slope
    FROM race_table as r, driver_races as dr
    WHERE dr.race_id = r.race_id
    AND d.driver_id = 1

这给出了我想要的数字，但我现在需要针对所有用户应用，按“斜率”排序

我正在尝试在 PostgreSQL 9.0 中构建一个查询，以获取特定列的最长连续行序列。

考虑下表：

lap_id (serial), lap_no (int), car_type (enum), race_id (int FK)

每个地方lap_no都是独一无二的(race_id, car_type)。

我希望查询为给定的race_idand生成最长的序列car_type，因此它将返回一个int最高的（或长的）。

使用以下数据：

1, 1, red, 1
2, 2, red, 1
3, 3, red, 1
4, 4, red, 1
5, 1, blue, 1
6, 5, red, 1
7, 2, blue, 1
8, 1, green, 1

因为car_type = red and race_id = 1查询将5作为lap_no字段的最长序列返回。

我在这里发现了一个类似的问题，但是我的情况更简单一些。

_{（我也想知道car_type所有种族的最长序列，但我打算自己解决这个问题。）}