cdsa cdsa - 4 months ago 16
SQL Question

postgres constraint exclusion: no performance gain

I have been trying to get constraint exclusion to work for postgres 9.5.

I am using some OSM data (13,191,400 rows) but whenever I execute a query it takes roughly the same time, no matter wether I use my partitoned table or the full table.

Explain does show differences though:
osm.poi is the full table and osmtest.poi the partitoned one.

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"


Here the partitioned one without 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)
...


Now with 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"


When actually executing the query though the time it takes is pretty similar for both queries:

full table: 23.6sec
without CE: 34.6
with CE: 19.7

How can there only be a 3-4 second increase in performance when comparing a sec scan over 13,191,400 and over 101,269 rows?

EDIT:

forgot to meintion the constraints:
all child tables are separated according to distinct values of fclass and have contraints like this:

CONSTRAINT poi_atm_fclass_check CHECK (fclass::text = 'atm'::text)

Answer Source

I just verified it on the postgres console and as Laurenz Albe pointed out in the comments, the bottleneck was not the server but pgAdmin.

Everything has been working as intended from the beginning.