为什么Where子句不会在视图的查询中被推下来? -- postgresql 领域 和 performance 领域 和 view 领域 和 optimization 领域 和 query-performance 领域 dba 相关 的问题

Why the WHERE clause is not pushed down in the view's query?


3
vote

问题

中文

与postgres 9.4,我经常做以下查询:

  SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN timeA AND timeB ORDER BY recipient, timestamp DESC;   

所以我决定创建一个视图:

  CREATE VIEW myView AS SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name ORDER BY recipient, timestamp DESC;   

我刚才意识到如果我查询我的视图,如 SELECT * FROM myView WHERE timestamp BETWEEN timeA AND timeB 我得到了显着更糟糕的性能。

做<代码> EXPLAIN ANALYZE 在两个查询中,我发现原因是第二个案例中的数据库带来了所有记录,左边连接然后应用 WHERE 条款。换句话说, WHERE 子句不会被推到视图的查询中。我还尝试从视图中删除 abcdefghijklmnORDER BY ,但仍然数据库在完整数据中执行 LEFT JOIN 而是在过滤的集合上。

这种行为的原因是什么?有没有办法在使用视图时可以获得类似的性能?

英文原文

With Postgres 9.4, I'm doing the following query quite often:

SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE timestamp BETWEEN timeA AND timeB ORDER BY recipient, timestamp DESC; 

So I decided to create a view:

CREATE VIEW myView AS SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name ORDER BY recipient, timestamp DESC; 

I just realized if I query my view like SELECT * FROM myView WHERE timestamp BETWEEN timeA AND timeB I get a significantly worse performance.

Doing EXPLAIN ANALYZE on both queries, I found out the reason is that the database in the second case brings up all the records, does the left join and then applies the WHERE clause. In other words, the WHERE clause is not pushed down into the view's query. I also tried to remove the ORDER BY from the view, but still the database performs the LEFT JOIN on full data rather on the filtered set.

What is the reason of this behavior? Is there a way I can get a comparable performance when using view?

              
   
   

回答列表

6
 
vote
vote
最佳答案
 

您可以创建这样的函数;

  CREATE OR REPLACE FUNCTION public.get_messages_by_timestamp (   time_a timestamp,   time_b timestamp ) RETURNS TABLE (   recipient varchar,   "timestamp" timestamp ) AS $$ BEGIN   RETURN QUERY     SELECT DISTINCT ON (m.recipient)          m.recipient,         m."timestamp"       FROM messages m       LEFT JOIN identities i ON m.recipient = i.name       WHERE          m."timestamp" BETWEEN time_a AND time_b       ORDER BY          m.recipient,         m."timestamp" DESC; END; $$ LANGUAGE 'plpgsql';   

然后,您可以使用像表中的函数

    SELECT *          FROM get_messages_by_timestamp('2015-01-01', '2015-01-02')   
 

You can create a function like this;

CREATE OR REPLACE FUNCTION public.get_messages_by_timestamp (   time_a timestamp,   time_b timestamp ) RETURNS TABLE (   recipient varchar,   "timestamp" timestamp ) AS $$ BEGIN   RETURN QUERY     SELECT DISTINCT ON (m.recipient)          m.recipient,         m."timestamp"       FROM messages m       LEFT JOIN identities i ON m.recipient = i.name       WHERE          m."timestamp" BETWEEN time_a AND time_b       ORDER BY          m.recipient,         m."timestamp" DESC; END; $$ LANGUAGE 'plpgsql'; 

Then you can use the function like a table

  SELECT *          FROM get_messages_by_timestamp('2015-01-01', '2015-01-02') 
 
 
12
 
vote

此查询:

  SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE messages.timestamp BETWEEN timeA AND timeB ORDER BY recipient, timestamp DESC;   

说:

对于timea和timeb 消息,找到收件人和每个收件人,找到一条消息(Timea和timeb之间的最新介绍)。


此查询(如果您使用该视图,您会收到该查询):

  SELECT      p.ID, p.Name FROM      patients p WHERE     EXISTS      (         SELECT 1         FROM controlvalues cv1         WHERE p.ID = cv1.PatientID               AND cv1.controlId = 1               AND cv1.value = '23'     )     AND     EXISTS     (         SELECT 1         FROM controlvalues cv2         WHERE p.ID = cv2.PatientID               AND cv2.controlId = 2               AND cv2.value = 'true'     ) ; 0  

说:

对于所有消息,找到收件人和每个收件人,找到一条消息,(常常最新),然后,显示这些消息timea和timeb。


结果,第一个查询将显示在时间A和之间的消息 b第二查询不会显示(因为可能存在相同收件人的一个或多个消息,而不是时间b)。

所以,查询是逻辑上不同的,条件不能(且不应该)为您的视图被按下。

如果要将参数传递给您的视图,请查看两个答案( @a_horse_with_no_name 和 @erwin brandstetter )在这个问题中如何使用 set返回函数< / strong>: 传递给postgreSql视图的"where" 参数?< / a>

 

This query:

SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE messages.timestamp BETWEEN timeA AND timeB ORDER BY recipient, timestamp DESC; 

says:

For all messages between timeA and timeB, find the recipients and for every recipient, find one message (the latest in between timeA and timeB).


This query (which you would get if you use the view):

SELECT * FROM    ( SELECT DISTINCT ON(recipient) * FROM messages     LEFT JOIN identities ON messages.recipient = identities.name     ORDER BY recipient, timestamp DESC   ) AS myView WHERE timestamp BETWEEN timeA AND timeB; 

says:

For all messages, find the recipients and for every recipient, find one message, (the latest in all times) and then, show only those messages that are between timeA and timeB.


As a result, the first query will show messages that are between time A and B which the second query will not show (because there may be one or more messages for the same recipient, later than time B).

So, the queries are logically different and the condition cannot (and shouldn't) be pushed down for your view.

If you want to have parameters passed to your view, look the two answers (by @a_horse_with_no_name and @Erwin Brandstetter) in this question for how to use a set returning function: Pass In xe2x80x9cWHERExe2x80x9d parameters to PostgreSQL View?

 
 
 
 

相关问题

1  postgres查询性能[关闭]  ( Postgres query performance ) 
关闭。这个问题需要详细信息或清晰度。它目前不接受答案。 想要改进这个问题?添加详细信息并阐明编辑此帖子的问题。 关闭 15天前。 ...

1  如何优化基于时间的数据?  ( How to optimize time based data ) 
我需要帮助优化相对较小的表格上基于时间的查询。 表看起来像这样: time int | tag varchar(21) | val decimal 我也有索引,如这种 create index _index on table (time, tag) 和查询如下所示: select * from...

0  是否有可能在飞行中将PostgreSQL服务器转换为Master-Master群集?  ( Is it possible to convert postgresql server to master master cluster on the fly ) 
有一个不应该停止的PostgreSQL服务器。 我想升级它。 是可能的: 配置新的空服务器, 将现有服务器转换为主站群集的第一个节点(无停止), 将新服务器连接到群集, 将DNS名称分配给第二服务器的IP, 和关闭旧服务器? ...

1  无法启动Wal Streaming,Replication Slot是活动的  ( Could not start wal streaming replication slot is active ) 
我想在两个数据库之间设置复制。 一个是PG 10中的Aurora数据库,另一个是PG 10中的RDS数据库。 但我面临一个问题,我有500 GB的数据传输,所以我逐个将表1逐个添加到复制并等待在添加另一个之前要准备好的状态。 但过了一段时间,我收到了这个错误: ERROR: terminating logical r...

0  基于与共享用户的国家分割数据库  ( Splitting database based on country with shares users ) 
99.9%的查询将为特定国家/地区(现在是美国或加拿大)进行过滤,所以我想拆分数据库国家。这样,查询不必踩到另一个国家的所有行,索引较少的列。 所有国家都应分享用户所以没有重复,但Postgres无法为另一个数据库创建外键。 stackoverflow.com 和 stackexchange.com 共享用户, ...

3  将表从Postgres迁移到SQL Server 2008  ( Migrating tables from postgres to sql server 2008 ) 
(我担心我希望完成的事情并不容易,但是我对它的许多谷歌曲都会产生相当日期的结果,所以在这里它是......) 我有一个超过100个表的postgres 8.4.x数据库。我需要在SQL Server 2008中重新创建这些表。我不关心数据,只是结构。 是否有任何光滑的工具,捷径或建议来完成此目的? Heck,我甚至甚...

1  PGPool负载平衡正在将所有查询发送到Master  ( Pgpool load balancing is sending all queries only to master ) 
我的两个postgreSQL服务器配置为流式复制,它正在运行正常。 pgpool配置为主从模式/负载平衡模式。 pgpool.conf: INSERT INTO `ad_analytics` (`id`, `ad_id`, `advertiser_id`, `publisher_id`, `visit...

0  安全地关闭看似错误的(但工作)PostgreSQL复制/存档  ( Safely shutting down seemingly misconfigured but working postgresql replicatio ) 
我在帮助管理两个postgreSQL服务器(一个主要,一个副本)加上运行WAL存档的单独服务器。我最初不参与设置复制和归档。配置相当陈旧,其中一些约会返回2015年和PostgreSQL 9.3,虽然我们现在正在运行9.5。可悲的是未记录配置更改的历史记录。建立设置的人仍然存在,但这是他们第一次设置复制。 在我们的V...

1  实现一致的邮箱  ( Implementing a consistent mailbox ) 
我试图在用户之间的短消息中实现类似邮箱的东西。我使用PostgreSQL 9.4.1与Sqlalchemy(Python3)。 有一个用户表: users = Table('users',metadata, Column('uuid',postgresql.UUID(as_uuid=True), ...

0  PostgreSQL索引大小:复合类型的惩罚?  ( Postgresql index size penalty for composite type ) 
我的问题是指postgreSQL 12.2。 我有两个几乎相同的表,结构和缺点,主要区别是第二表的主键是复合类型,而第一表包括直接类型的组件。 CREATE TABLE first ( a INTEGER, b SMALLINT, c SMALLINT, PRIMARY KEY (a,b,...

0  如何重新定义PostgreSQL视图的SQL,而不使程序无效  ( How to redefine the sql of a postgresql view without invalidating the programs u ) 
我有一个复杂的代码,执行FIFO分配。我在应用程序中有3个不同的地方需要这个逻辑,将3个不同的表一起链接在一起。在MSaccess中,我可以通过使用动态定义的ViewName打开Recordset来实现此目的(以前创建的底层视图,但在程序中,我可以在设置光标的调用中使用变量名称)。这样我可以使用相同的程序来为所有3组...

0  如何在PostgreSQL中交换阵列值。?  ( How to swap array values in postgresql ) 
如何在PostgreSQL中交换阵列值。例如,我有一个表,该表有一个列,其数据类型为 bigint[] 。它有以下内容。 值: {111, 222, 333} 我所需的输出是 {333, 111, 222} 我找不到任何内置的功能,以实现array_replace以外的。 ...

0  后期范围的PostgreSQL累计计数  ( Postgresql cumulative counts in date range ) 
我正在尝试获取行的累积计数(通过 abcdefghijklmnabcdefghijklmngroup_id )表示该行处于活动时间的时间段。 我有一个这样的桌子: group_id | id | type | start_date | end_date ----------+--------+----...

0  在网站域名中搜索部分匹配  ( Search for partial matches in website domain names ) 
我有一个postgres数据库,其中包含有关网站的信息和一个名为 sites 的表,其中包含列 host 。这已经有一个文本模式操作的索引,这是从域名的开始中搜索的优秀: WHERE host LIKE 'www.bran% 但对于部分匹配而不是那么好,例如 WHERE host LIKE '%.bran%' 扫描。...

0  Postgres流程100%CPU利用率  ( 100 cpu utilization for postgres processes ) 
我们在单个表中具有2.6M行数据,并与具有相似大小的其他2个表加入。在运行SELECT查询时,它正在占用100%CPU,并且查询永远挂起。我们尝试了所有这三个表的纠正。 reindexing查询后仍在服用100%的CPU,但持续1分钟并出来。 我们已经尝试了以下参数来调整Postgres但没有性能改进。 shar...




© 2021 it.wenda123.org All Rights Reserved. 问答之家 版权所有


Licensed under cc by-sa 3.0 with attribution required.