- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
我一直在尝试让约束排除适用于 postgres 9.5。
我正在使用一些 OSM 数据(13,191,400 行),但无论我使用分区表还是完整表,每当我执行查询时,它花费的时间大致相同。
虽然 Explain 确实显示了差异:osm.poi 是完整表,osmtest.poi 是分区表。
EXPLAIN ANALYZE SELECT * FROM osm.poi WHERE fclass='atm'
"Seq Scan on poi (cost=0.00..394124.50 rows=38200 width=128) (actual time=416.442..13145.447 rows=101269 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 13089480"
"Planning time: 0.326 ms"
"Execution time: 13151.487 ms"
这里是没有 CE 的分区:
EXPLAIN ANALYZE SELECT * FROM osmtest.poi WHERE fclass='atm'
"Append (cost=0.00..394119.71 rows=101414 width=109) (actual time=8411.957..18072.714 rows=101269 loops=1)"
" -> Seq Scan on poi (cost=0.00..0.00 rows=1 width=694) (actual time=0.002..0.002 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" -> Seq Scan on poi_doityourself (cost=0.00..2198.31 rows=1 width=127) (actual time=641.416..641.416 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 70505"
" -> Seq Scan on poi_recycling_paper (cost=0.00..315.62 rows=1 width=141) (actual time=116.144..116.144 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 10850"
" -> Seq Scan on poi_memorial (cost=0.00..3804.61 rows=1 width=141) (actual time=122.410..122.410 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 120609"
" -> Seq Scan on poi_car_sharing (cost=0.00..187.45 rows=1 width=135) (actual time=5.752..5.752 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 5876"
" -> Seq Scan on poi_florist (cost=0.00..1126.12 rows=1 width=119) (actual time=43.613..43.613 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 37850"
" -> Seq Scan on poi_car_repair (cost=0.00..4.83 rows=1 width=127) (actual time=7.631..7.631 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 146"
" -> Seq Scan on poi_sports_shop (cost=0.00..473.83 rows=1 width=123) (actual time=27.964..27.964 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 15266"
" -> Seq Scan on poi_courthouse (cost=0.00..473.20 rows=1 width=150) (actual time=7.372..7.372 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 13776"
" -> Seq Scan on poi_water_well (cost=0.00..2152.34 rows=1 width=121) (actual time=79.398..79.398 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 75147"
" -> Seq Scan on poi_motel (cost=0.00..849.03 rows=1 width=124) (actual time=15.714..15.714 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 27362"
" -> Seq Scan on poi_convenience (cost=0.00..8718.44 rows=1 width=118) (actual time=152.093..152.093 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 289235"
" -> Seq Scan on poi_outdoor_shop (cost=0.00..192.80 rows=1 width=126) (actual time=72.358..72.358 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 6144"
" -> Seq Scan on poi_caravan_site (cost=0.00..529.84 rows=1 width=135) (actual time=8.495..8.495 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 17027"
" -> Seq Scan on poi_lighthouse (cost=0.00..244.43 rows=1 width=131) (actual time=4.086..4.086 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 8274"
" -> Seq Scan on poi_camp_site (cost=0.00..2142.51 rows=1 width=126) (actual time=344.405..344.405 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 70361"
" -> Seq Scan on poi_playground (cost=0.00..8907.36 rows=1 width=130) (actual time=249.476..249.476 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 325549"
" -> Seq Scan on poi_vending_cigarette (cost=0.00..414.33 rows=1 width=133) (actual time=27.361..27.361 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 13866"
" -> Seq Scan on poi_stationery (cost=0.00..433.79 rows=1 width=123) (actual time=10.555..10.555 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 14303"
" -> Seq Scan on poi_observation_tower (cost=0.00..251.44 rows=1 width=138) (actual time=4.354..4.354 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 8355"
" -> Seq Scan on poi_monument (cost=0.00..1262.15 rows=1 width=140) (actual time=65.268..65.268 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 40332"
" -> Seq Scan on poi_nightclub (cost=0.00..414.66 rows=1 width=116) (actual time=33.113..33.113 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 13573"
" -> Seq Scan on poi_university (cost=0.00..1452.06 rows=1 width=159) (actual time=60.754..60.754 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 41365"
" -> Seq Scan on poi_stadium (cost=0.00..1117.80 rows=1 width=131) (actual time=296.267..296.267 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 37744"
" -> Seq Scan on poi_waste_basket (cost=0.00..4733.75 rows=1 width=127) (actual time=440.303..440.303 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 163740"
" -> Seq Scan on poi_hospital (cost=0.00..4456.52 rows=1 width=148) (actual time=324.279..324.279 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 131722"
" -> Seq Scan on poi_swimming_pool (cost=0.00..19665.28 rows=1 width=137) (actual time=755.354..755.354 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 695862"
" -> Seq Scan on poi_public_building (cost=0.00..3493.40 rows=1 width=158) (actual time=113.032..113.032 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 104432"
" -> Seq Scan on poi_biergarten (cost=0.00..247.45 rows=1 width=125) (actual time=27.892..27.892 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 8116"
" -> Seq Scan on poi_archaeological (cost=0.00..1382.66 rows=1 width=139) (actual time=46.234..46.234 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 44293"
" -> Seq Scan on poi_fire_hydrant (cost=0.00..15179.20 rows=1 width=118) (actual time=679.981..679.981 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 541456"
" -> Seq Scan on poi_post_office (cost=0.00..4136.10 rows=1 width=138) (actual time=85.252..85.252 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 128008"
" -> Seq Scan on poi_garden_centre (cost=0.00..426.77 rows=1 width=130) (actual time=34.062..34.062 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 13662"
" -> Seq Scan on poi_doctors (cost=0.00..1930.45 rows=1 width=139) (actual time=64.683..64.683 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 59076"
" -> Seq Scan on poi_food_court (cost=0.00..92.12 rows=1 width=127) (actual time=11.586..11.586 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 2970"
" -> Seq Scan on poi_shoe_shop (cost=0.00..892.29 rows=1 width=119) (actual time=55.079..55.079 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 29703"
" -> Seq Scan on poi_toy_shop (cost=0.00..342.96 rows=1 width=119) (actual time=30.532..30.532 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 11357"
" -> Seq Scan on poi_kiosk (cost=0.00..1583.11 rows=1 width=114) (actual time=26.805..26.805 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 55929"
" -> Seq Scan on poi_clothes (cost=0.00..4021.28 rows=1 width=117) (actual time=558.187..558.187 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 135542"
" -> Seq Scan on poi_hotel (cost=0.00..6773.75 rows=1 width=123) (actual time=628.795..628.795 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 219340"
" -> Seq Scan on poi_tourist_info (cost=0.00..1887.59 rows=1 width=138) (actual time=137.643..137.643 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 61887"
" -> Seq Scan on poi_alpine_hut (cost=0.00..348.01 rows=1 width=129) (actual time=35.760..35.760 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 11121"
" -> Seq Scan on poi_shelter (cost=0.00..3935.41 rows=1 width=119) (actual time=162.929..162.929 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 138673"
" -> Seq Scan on poi_wayside_shrine (cost=0.00..1073.25 rows=1 width=134) (actual time=329.421..329.421 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 36980"
" -> Seq Scan on poi_fire_station (cost=0.00..2799.75 rows=1 width=155) (actual time=141.533..141.533 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 82540"
" -> Seq Scan on poi_tourist_guidepost (cost=0.00..5397.58 rows=1 width=129) (actual time=104.954..104.954 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 183166"
" -> Seq Scan on poi_ruins (cost=0.00..1940.60 rows=1 width=132) (actual time=69.253..69.253 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 66688"
" -> Seq Scan on poi_town_hall (cost=0.00..2564.99 rows=1 width=135) (actual time=38.129..38.129 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 80479"
" -> Seq Scan on poi_water_works (cost=0.00..408.27 rows=1 width=141) (actual time=34.019..34.019 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 13622"
" -> Seq Scan on poi_battlefield (cost=0.00..50.71 rows=1 width=189) (actual time=38.927..38.927 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 1577"
" -> Seq Scan on poi_car_rental (cost=0.00..343.55 rows=1 width=123) (actual time=7.157..7.157 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 11004"
" -> Seq Scan on poi_pub (cost=0.00..3605.46 rows=1 width=107) (actual time=239.877..239.877 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 123397"
" -> Seq Scan on poi_park (cost=0.00..1655.35 rows=1 width=127) (actual time=227.761..227.761 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 52668"
" -> Seq Scan on poi_pitch (cost=0.00..26988.76 rows=1 width=123) (actual time=566.460..566.460 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 989661"
" -> Seq Scan on poi_atm (cost=0.00..2805.86 rows=101269 width=109) (actual time=0.027..26.193 rows=101269 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" -> Seq Scan on poi_recycling_glass (cost=0.00..1976.30 rows=1 width=141) (actual time=348.347..348.347 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 67944"
" -> Seq Scan on poi_jeweller (cost=0.00..713.05 rows=1 width=117) (actual time=41.594..41.594 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 23524"
" -> Seq Scan on poi_recycling_clothes (cost=0.00..395.76 rows=1 width=137) (actual time=6.511..6.511 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 13261"
" -> Seq Scan on poi_zoo (cost=0.00..163.03 rows=1 width=125) (actual time=26.208..26.208 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 5282"
" -> Seq Scan on poi_ice_rink (cost=0.00..89.20 rows=1 width=133) (actual time=19.734..19.734 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 3056"
" -> Seq Scan on poi_car_wash (cost=0.00..1257.38 rows=1 width=121) (actual time=18.300..18.300 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 43150"
" -> Seq Scan on poi_video_shop (cost=0.00..115.84 rows=1 width=119) (actual time=13.771..13.771 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 3827"
" -> Seq Scan on poi_butcher (cost=0.00..1217.72 rows=1 width=122) (actual time=67.814..67.814 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 40298"
" -> Seq Scan on poi_vending_parking (cost=0.00..450.29 rows=1 width=131) (actual time=7.032..7.032 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 15063"
" -> Seq Scan on poi_track (cost=0.00..1200.14 rows=1 width=124) (actual time=42.848..42.848 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 43611"
" -> Seq Scan on poi_windmill (cost=0.00..201.62 rows=1 width=122) (actual time=31.632..31.632 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 6930"
" -> Seq Scan on poi_graveyard (cost=0.00..238.81 rows=1 width=131) (actual time=3.960..3.960 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 8065"
" -> Seq Scan on poi_dentist (cost=0.00..1203.01 rows=1 width=136) (actual time=60.320..60.320 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" Rows Removed by Filter: 37361"
" -> Seq Scan on poi_hunting_stand (cost=0.00..2443.93 rows=1 width=132)
...
现在有了 CE:
EXPLAIN ANALYZE SELECT * FROM osmtest.poi WHERE fclass='atm'
"Append (cost=0.00..2805.86 rows=101270 width=109) (actual time=0.009..24.077 rows=101269 loops=1)"
" -> Seq Scan on poi (cost=0.00..0.00 rows=1 width=694) (actual time=0.001..0.001 rows=0 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
" -> Seq Scan on poi_atm (cost=0.00..2805.86 rows=101269 width=109) (actual time=0.008..18.278 rows=101269 loops=1)"
" Filter: ((fclass)::text = 'atm'::text)"
"Planning time: 8.189 ms"
"Execution time: 26.652 ms"
当实际执行查询时,尽管两个查询所花费的时间非常相似:
全表:23.6秒没有CE:34.6CE:19.7
在比较超过 13,191,400 行和超过 101,269 行的秒扫描时,性能怎么可能只提高了 3-4 秒?
编辑:
忘记提及约束:所有子表都根据 fclass 的不同值分开,并且具有如下约束:
CONSTRAINT poi_atm_fclass_check CHECK (fclass::text = 'atm'::text)
最佳答案
我刚刚在 postgres 控制台上验证了它,正如 Laurenz Albe 在评论中指出的那样,瓶颈不是服务器而是 pgAdmin。
一切从一开始就按预期进行。
关于sql - postgres 约束排除 : no performance gain,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/45102031/
我可以添加一个检查约束来确保所有值都是唯一的,但允许默认值重复吗? 最佳答案 您可以使用基于函数的索引 (FBI) 来实现此目的: create unique index idx on my_tabl
嗨,我在让我的约束在grails项目中工作时遇到了一些麻烦。我试图确保Site_ID的字段不留为空白,但仍接受空白输入。另外,我尝试设置字段显示的顺序,但即使尝试时也无法反射(reflect)在页面上
我似乎做错了,我正在尝试将一个字段修改为外键,并使用级联删除...我做错了什么? ALTER TABLE my_table ADD CONSTRAINT $4 FOREIGN KEY my_field
阅读目录 1、约束的基本概念 2、约束的案例实践 3、外键约束介绍 4、外键约束展示 5、删除
SQLite 约束 约束是在表的数据列上强制执行的规则。这些是用来限制可以插入到表中的数据类型。这确保了数据库中数据的准确性和可靠性。 约束可以是列级或表级。列级约束仅适用于列,表级约束被应用到整
我在 SerenityOS project 中偶然发现了这段代码: template void dbgln(CheckedFormatString&& fmtstr, const Parameters
我有表 tariffs,有两列:(tariff_id, reception) 我有表 users,有两列:(user_id, reception) 我的表 users_tariffs 有两列:(use
在 Derby 服务器中,如何使用模式的系统表中的信息来创建选择语句以检索每个表的约束名称? 最佳答案 相关手册是Derby Reference Manual .有许多可用版本:10.13 是 201
我正在使用 z3py 进行编码。请参阅以下示例。 from z3 import * x = Int('x') y = Int('y') s = Solver() s.add(x+y>3) if s.c
非常快速和简单的问题。我正在运行一个脚本来导入数据并声明了一个临时表并将检查约束应用于该表。显然,如果脚本运行不止一次,我会检查临时表是否已经存在,如果存在,我会删除并重新创建临时表。这也会删除并重新
我有一个浮点变量 x在一个线性程序中,它应该是 0或两个常量之间 CONSTANT_A和 CONSTANT_B : LP.addConstraint(x == 0 OR CONSTANT_A <= x
我在使用grails的spring-data-neo4j获得唯一约束时遇到了一些麻烦。 我怀疑这是因为我没有正确连接它,但是存储库正在扫描和连接,并且CRUD正在工作,所以我不确定我做错了什么。 我正
这个问题在这里已经有了答案: Is there a constraint that restricts my generic method to numeric types? (24 个回答) 7年前
我有一个浮点变量 x在一个线性程序中,它应该是 0或两个常量之间 CONSTANT_A和 CONSTANT_B : LP.addConstraint(x == 0 OR CONSTANT_A <= x
在iOS的 ScrollView 中将图像和带有动态文本(动态高度)的标签居中的最佳方法是什么? 我必须添加哪些约束?我真的无法弄清楚它是如何工作的,也许我无法处理它,因为我是一名 Android 开
考虑以下代码: class Foo f class Bar b newtype D d = D call :: Proxy c -> (forall a . c a => a -> Bool) ->
我有一个类型类,它强加了 KnownNat约束: class KnownNat (Card a) => HasFin a where type Card a :: Nat ... 而且,我有几
我知道REST原则上与HTTP无关。 HTTP是协议,REST是用于通过Web传输hypermedia的体系结构样式。 REST可以使用诸如HTTP,FTP等的任何应用程序层协议。关于REST的讨论很
我有这样的情况,我必须在数据库中存储复杂的数据编号。类似于 21/2011,其中 21 是文件编号,但 2011 是文件年份。所以我需要一些约束来处理唯一性,因为有编号为 21/2010 和 21/2
我有一个 MySql (InnoDb) 表,表示对许多类型的对象之一所做的评论。因为我正在使用 Concrete Table Inheritance ,对于下面显示的每种类型的对象(商店、类别、项目)
我是一名优秀的程序员,十分优秀!