postgresql - Postgresql 使用 where 子句选择视图太慢
问题描述
视图和表执行时间的问题是不同的。
为什么两个查询之间有这么多差异?
使用“LIMIT”子句的查询 2 查询变得更快,但现在非常慢。(就像 Select * From ... limit 1000)
注意:我在 VIEW 中使用的功能不会做任何艰苦的工作。语言 plpgsql。
查询1:执行时间=106.726 ms;
EXPLAIN ANALYZE SELECT * FROM dbo.ST_Foto where StationId='Sample Guid'
and Tarih <='2018-09-12' -- Table
查询1解释分析;
"Seq Scan on st_foto (cost=0.00..4559.68 rows=103878 width=215) (actual time=0.036..81.046 rows=103938 loops=1)"
" Filter: ((tarih <= '2018-09-12 00:00:00'::timestamp without time zone) AND (stationid = 'ac5a2189-f931-47c0-9845-d0ff8eac7cb7'::uuid))"
" Rows Removed by Filter: 20174"
"Planning time: 0.359 ms"
"Execution time: 106.726 ms"
查询2执行时间=30562.223 ms;
EXPLAIN ANALYZE SELECT * FROM dbo.VWST_Foto where StationId='Sample Guid'
and Tarih <='2018-09-12' -- VIEW
查询2解释分析;
"Seq Scan on st_foto (cost=0.00..161415.46 rows=103878 width=439) (actual time=1.364..30510.830 rows=103938 loops=1)"
" Filter: ((tarih <= '2018-09-12 00:00:00'::timestamp without time zone) AND (stationid = 'ac5a2189-f931-47c0-9845-d0ff8eac7cb7'::uuid))"
" Rows Removed by Filter: 20174"
"Planning time: 0.517 ms"
"Execution time: 30562.223 ms"
看法 :
CREATE VIEW dbo.vwst_foto AS
SELECT id,
created,
changed,
createdby,
changedby,
intid,
hardwareid,
stationid,
zoom,
tarih,
tarihgmt2,
gunesradyasyon,
bitkiboyu,
contrast,
yesilorani,
preset,
rgb,
fft,
foto,
dbo.fn_hardwarefullname(hardwareid) AS hardware_title,
dbo.fn_stationfullname(stationid) AS station_title,
dbo.fn_userfullname(createdby) AS createdby_title,
dbo.fn_userfullname(changedby) AS changedby_title,
''::text AS fotograflink,
/* the basic func */ dbo.fn_enumkeytodescription('ST_Foto'::character varying, 'Zoom'::character varying, zoom::character varying) AS zoom_title,
/* the basic func */ dbo.fn_enumkeytodescription('ST_Foto'::character varying, 'Preset'::character varying, preset::character varying) AS preset_title
FROM dbo.st_foto;
我的服务器有 32 GB 内存。
此外
解释(分析,缓冲)
分析 :
对于视图:(查询 2)
"Seq Scan on st_foto (cost=0.00..161415.46 rows=103878 width=439) (actual time=0.552..30695.394 rows=103938 loops=1)"
" Filter: ((tarih <= '2018-09-12 00:00:00'::timestamp without time zone) AND (stationid = 'ac5a2189-f931-47c0-9845-d0ff8eac7cb7'::uuid))"
" Rows Removed by Filter: 20174"
" Buffers: shared hit=1297179"
"Planning time: 0.489 ms"
"Execution time: 30749.816 ms"
对于表:(查询 1)
"Seq Scan on st_foto (cost=0.00..4559.68 rows=103878 width=215) (actual time=0.035..80.967 rows=103938 loops=1)"
" Filter: ((tarih <= '2018-09-12 00:00:00'::timestamp without time zone) AND (stationid = 'ac5a2189-f931-47c0-9845-d0ff8eac7cb7'::uuid))"
" Rows Removed by Filter: 20174"
" Buffers: shared hit=2698"
"Planning time: 0.352 ms"
"Execution time: 107.618 ms"
解决方案
推荐阅读
- python - 在随机坐标数组中使用colliderect
- git - 如何最好地从 ADO 中的提交/工作项创建发行说明
- c++ - std::unique_ptr 的类内初始化为不完整类型
- javascript - 什么是/这种类型的评论/?甚至找不到提及它
- php - 添加 WordPress 自定义管理菜单时可以使用文件夹 url(自定义 url)吗?
- javascript - 放置 404(未找到)
- python - 使用 Json 解码的 Flutter App 问题 - 来自套接字的数据
- wsgi - 这些术语 [ASGI、WSGI、Channels、WebSockets] 之间有什么更密切的关系?
- java - JPA 与存储在不同数据库中的用户实体的多对多关系
- java - Java 需要另一个 Enter 来完成