NotificationsYou must be signed in to change notification settings
Fork6
Star31

Commit178ec46

committed

Fixes for multirange selectivity estimation

* Fix enumeration of the multirange operators in calc_multirangesel() and calc_multirangesel() switches. * Add more regression tests for matching to empty ranges/multiranges.Reported-by: Alexander LakhinDiscussion:https://postgr.es/m/c5269c65-f967-77c5-ff7c-15e621c47f6a%40gmail.comAuthor: Alexander KorotkovBackpatch-through: 14, where multiranges were introduced

1 parentb71a9cb commit178ec46Copy full SHA for 178ec46

File tree

3 files changed

+272

-13

lines changed

src
- backend/utils/adt
  - multirangetypes_selfuncs.c
- test/regress
  - expected
    - multirangetypes.out
  - sql
    - multirangetypes.sql

3 files changed

+272

-13

lines changed

`‎src/backend/utils/adt/multirangetypes_selfuncs.c`

Lines changed: 25 additions & 13 deletions

Original file line number	Diff line number	Diff line change
`@@ -347,16 +347,15 @@ calc_multirangesel(TypeCacheEntry typcache, VariableStatData vardata,`
`347`	`347`	`switch (operator)`
`348`	`348`	`{`
`349`	`349`	`/* these return false if either argument is empty */`
`350`		`-caseOID_RANGE_OVERLAPS_MULTIRANGE_OP:`
`351`	`350`	`caseOID_MULTIRANGE_OVERLAPS_RANGE_OP:`
`352`	`351`	`caseOID_MULTIRANGE_OVERLAPS_MULTIRANGE_OP:`
`353`		`-caseOID_RANGE_OVERLAPS_LEFT_MULTIRANGE_OP:`
`354`	`352`	`caseOID_MULTIRANGE_OVERLAPS_LEFT_RANGE_OP:`
`355`	`353`	`caseOID_MULTIRANGE_OVERLAPS_LEFT_MULTIRANGE_OP:`
`356`		`-caseOID_RANGE_OVERLAPS_RIGHT_MULTIRANGE_OP:`
`357`	`354`	`caseOID_MULTIRANGE_OVERLAPS_RIGHT_RANGE_OP:`
`358`	`355`	`caseOID_MULTIRANGE_OVERLAPS_RIGHT_MULTIRANGE_OP:`
	`356`	`+caseOID_MULTIRANGE_LEFT_RANGE_OP:`
`359`	`357`	`caseOID_MULTIRANGE_LEFT_MULTIRANGE_OP:`
	`358`	`+caseOID_MULTIRANGE_RIGHT_RANGE_OP:`
`360`	`359`	`caseOID_MULTIRANGE_RIGHT_MULTIRANGE_OP:`
`361`	`360`	`/* nothing is less than an empty multirange */`
`362`	`361`	`caseOID_MULTIRANGE_LESS_OP:`
`@@ -367,7 +366,7 @@ calc_multirangesel(TypeCacheEntry typcache, VariableStatData vardata,`
`367`	`366`	`* only empty multiranges can be contained by an empty`
`368`	`367`	`* multirange`
`369`	`368`	`*/`
`370`		`-caseOID_MULTIRANGE_RANGE_CONTAINED_OP:`
	`369`	`+caseOID_RANGE_MULTIRANGE_CONTAINED_OP:`
`371`	`370`	`caseOID_MULTIRANGE_MULTIRANGE_CONTAINED_OP:`
`372`	`371`	`/* only empty ranges are <= an empty multirange */`
`373`	`372`	`caseOID_MULTIRANGE_LESS_EQUAL_OP:`
`@@ -388,8 +387,18 @@ calc_multirangesel(TypeCacheEntry typcache, VariableStatData vardata,`
`388`	`387`	`break;`
`389`	`388`
`390`	`389`	`/* an element cannot be empty */`
`391`		`-caseOID_MULTIRANGE_ELEM_CONTAINED_OP:`
`392`	`390`	`caseOID_MULTIRANGE_CONTAINS_ELEM_OP:`
	`391`	`+`
	`392`	`+/* filtered out by multirangesel() */`
	`393`	`+caseOID_RANGE_OVERLAPS_MULTIRANGE_OP:`
	`394`	`+caseOID_RANGE_OVERLAPS_LEFT_MULTIRANGE_OP:`
	`395`	`+caseOID_RANGE_OVERLAPS_RIGHT_MULTIRANGE_OP:`
	`396`	`+caseOID_RANGE_LEFT_MULTIRANGE_OP:`
	`397`	`+caseOID_RANGE_RIGHT_MULTIRANGE_OP:`
	`398`	`+caseOID_RANGE_CONTAINS_MULTIRANGE_OP:`
	`399`	`+caseOID_MULTIRANGE_ELEM_CONTAINED_OP:`
	`400`	`+caseOID_MULTIRANGE_RANGE_CONTAINED_OP:`
	`401`	`+`
`393`	`402`	`default:`
`394`	`403`	`elog(ERROR,"unexpected operator %u",operator);`
`395`	`404`	`selec=0.0;/* keep compiler quiet */`
`@@ -416,8 +425,7 @@ calc_multirangesel(TypeCacheEntry typcache, VariableStatData vardata,`
`416`	`425`	`* calculations, realizing that the histogram covers only the`
`417`	`426`	`* non-null, non-empty values.`
`418`	`427`	`*/`
`419`		`-if (operator==OID_MULTIRANGE_ELEM_CONTAINED_OP\|\|`
`420`		`-operator==OID_MULTIRANGE_RANGE_CONTAINED_OP\|\|`
	`428`	`+if (operator==OID_RANGE_MULTIRANGE_CONTAINED_OP\|\|`
`421`	`429`	`operator==OID_MULTIRANGE_MULTIRANGE_CONTAINED_OP)`
`422`	`430`	`{`
`423`	`431`	`/* empty is contained by anything non-empty */`
`@@ -575,7 +583,6 @@ calc_hist_selectivity(TypeCacheEntry typcache, VariableStatData vardata,`
`575`	`583`	`hist_lower,nhist, true);`
`576`	`584`	`break;`
`577`	`585`
`578`		`-caseOID_RANGE_LEFT_MULTIRANGE_OP:`
`579`	`586`	`caseOID_MULTIRANGE_LEFT_RANGE_OP:`
`580`	`587`	`caseOID_MULTIRANGE_LEFT_MULTIRANGE_OP:`
`581`	`588`	`/* var << const when upper(var) < lower(const) */`
`@@ -584,7 +591,6 @@ calc_hist_selectivity(TypeCacheEntry typcache, VariableStatData vardata,`
`584`	`591`	`hist_upper,nhist, false);`
`585`	`592`	`break;`
`586`	`593`
`587`		`-caseOID_RANGE_RIGHT_MULTIRANGE_OP:`
`588`	`594`	`caseOID_MULTIRANGE_RIGHT_RANGE_OP:`
`589`	`595`	`caseOID_MULTIRANGE_RIGHT_MULTIRANGE_OP:`
`590`	`596`	`/* var >> const when lower(var) > upper(const) */`
`@@ -593,7 +599,6 @@ calc_hist_selectivity(TypeCacheEntry typcache, VariableStatData vardata,`
`593`	`599`	`hist_lower,nhist, true);`
`594`	`600`	`break;`
`595`	`601`
`596`		`-caseOID_RANGE_OVERLAPS_RIGHT_MULTIRANGE_OP:`
`597`	`602`	`caseOID_MULTIRANGE_OVERLAPS_RIGHT_RANGE_OP:`
`598`	`603`	`caseOID_MULTIRANGE_OVERLAPS_RIGHT_MULTIRANGE_OP:`
`599`	`604`	`/* compare lower bounds */`
`@@ -602,7 +607,6 @@ calc_hist_selectivity(TypeCacheEntry typcache, VariableStatData vardata,`
`602`	`607`	`hist_lower,nhist, false);`
`603`	`608`	`break;`
`604`	`609`
`605`		`-caseOID_RANGE_OVERLAPS_LEFT_MULTIRANGE_OP:`
`606`	`610`	`caseOID_MULTIRANGE_OVERLAPS_LEFT_RANGE_OP:`
`607`	`611`	`caseOID_MULTIRANGE_OVERLAPS_LEFT_MULTIRANGE_OP:`
`608`	`612`	`/* compare upper bounds */`
`@@ -611,7 +615,6 @@ calc_hist_selectivity(TypeCacheEntry typcache, VariableStatData vardata,`
`611`	`615`	`hist_upper,nhist, true);`
`612`	`616`	`break;`
`613`	`617`
`614`		`-caseOID_RANGE_OVERLAPS_MULTIRANGE_OP:`
`615`	`618`	`caseOID_MULTIRANGE_OVERLAPS_RANGE_OP:`
`616`	`619`	`caseOID_MULTIRANGE_OVERLAPS_MULTIRANGE_OP:`
`617`	`620`	`caseOID_MULTIRANGE_CONTAINS_ELEM_OP:`
`@@ -647,7 +650,6 @@ calc_hist_selectivity(TypeCacheEntry typcache, VariableStatData vardata,`
`647`	`650`	`lslot.values,lslot.nvalues);`
`648`	`651`	`break;`
`649`	`652`
`650`		`-caseOID_MULTIRANGE_RANGE_CONTAINED_OP:`
`651`	`653`	`caseOID_MULTIRANGE_MULTIRANGE_CONTAINED_OP:`
`652`	`654`	`caseOID_RANGE_MULTIRANGE_CONTAINED_OP:`
`653`	`655`	`if (const_lower.infinite)`
`@@ -675,6 +677,16 @@ calc_hist_selectivity(TypeCacheEntry typcache, VariableStatData vardata,`
`675`	`677`	`}`
`676`	`678`	`break;`
`677`	`679`
	`680`	`+/* filtered out by multirangesel() */`
	`681`	`+caseOID_RANGE_OVERLAPS_MULTIRANGE_OP:`
	`682`	`+caseOID_RANGE_OVERLAPS_LEFT_MULTIRANGE_OP:`
	`683`	`+caseOID_RANGE_OVERLAPS_RIGHT_MULTIRANGE_OP:`
	`684`	`+caseOID_RANGE_LEFT_MULTIRANGE_OP:`
	`685`	`+caseOID_RANGE_RIGHT_MULTIRANGE_OP:`
	`686`	`+caseOID_RANGE_CONTAINS_MULTIRANGE_OP:`
	`687`	`+caseOID_MULTIRANGE_ELEM_CONTAINED_OP:`
	`688`	`+caseOID_MULTIRANGE_RANGE_CONTAINED_OP:`
	`689`	`+`
`678`	`690`	`default:`
`679`	`691`	`elog(ERROR,"unknown multirange operator %u",operator);`
`680`	`692`	`hist_selec=-1.0;/* keep compiler quiet */`

`‎src/test/regress/expected/multirangetypes.out`

Lines changed: 210 additions & 0 deletions

Original file line number	Diff line number	Diff line change
`@@ -2241,12 +2241,114 @@ analyze test_multirange_gist;`
`2241`	`2241`	`SET enable_seqscan = t;`
`2242`	`2242`	`SET enable_indexscan = f;`
`2243`	`2243`	`SET enable_bitmapscan = f;`
	`2244`	`+select count(*) from test_multirange_gist where mr = '{}'::int4multirange;`
	`2245`	`+ count`
	`2246`	`+-------`
	`2247`	`+ 500`
	`2248`	`+(1 row)`
	`2249`	`+`
`2244`	`2250`	`select count(*) from test_multirange_gist where mr @> 'empty'::int4range;`
`2245`	`2251`	`count`
`2246`	`2252`	`-------`
`2247`	`2253`	`3700`
`2248`	`2254`	`(1 row)`
`2249`	`2255`
	`2256`	`+select count(*) from test_multirange_gist where mr && 'empty'::int4range;`
	`2257`	`+ count`
	`2258`	`+-------`
	`2259`	`+ 0`
	`2260`	`+(1 row)`
	`2261`	`+`
	`2262`	`+select count(*) from test_multirange_gist where mr <@ 'empty'::int4range;`
	`2263`	`+ count`
	`2264`	`+-------`
	`2265`	`+ 500`
	`2266`	`+(1 row)`
	`2267`	`+`
	`2268`	`+select count(*) from test_multirange_gist where mr << 'empty'::int4range;`
	`2269`	`+ count`
	`2270`	`+-------`
	`2271`	`+ 0`
	`2272`	`+(1 row)`
	`2273`	`+`
	`2274`	`+select count(*) from test_multirange_gist where mr >> 'empty'::int4range;`
	`2275`	`+ count`
	`2276`	`+-------`
	`2277`	`+ 0`
	`2278`	`+(1 row)`
	`2279`	`+`
	`2280`	`+select count(*) from test_multirange_gist where mr &< 'empty'::int4range;`
	`2281`	`+ count`
	`2282`	`+-------`
	`2283`	`+ 0`
	`2284`	`+(1 row)`
	`2285`	`+`
	`2286`	`+select count(*) from test_multirange_gist where mr &> 'empty'::int4range;`
	`2287`	`+ count`
	`2288`	`+-------`
	`2289`	`+ 0`
	`2290`	`+(1 row)`
	`2291`	`+`
	`2292`	`+select count(*) from test_multirange_gist where mr -\|- 'empty'::int4range;`
	`2293`	`+ count`
	`2294`	`+-------`
	`2295`	`+ 0`
	`2296`	`+(1 row)`
	`2297`	`+`
	`2298`	`+select count(*) from test_multirange_gist where mr @> '{}'::int4multirange;`
	`2299`	`+ count`
	`2300`	`+-------`
	`2301`	`+ 3700`
	`2302`	`+(1 row)`
	`2303`	`+`
	`2304`	`+select count(*) from test_multirange_gist where mr @> '{}'::int4multirange;`
	`2305`	`+ count`
	`2306`	`+-------`
	`2307`	`+ 3700`
	`2308`	`+(1 row)`
	`2309`	`+`
	`2310`	`+select count(*) from test_multirange_gist where mr && '{}'::int4multirange;`
	`2311`	`+ count`
	`2312`	`+-------`
	`2313`	`+ 0`
	`2314`	`+(1 row)`
	`2315`	`+`
	`2316`	`+select count(*) from test_multirange_gist where mr <@ '{}'::int4multirange;`
	`2317`	`+ count`
	`2318`	`+-------`
	`2319`	`+ 500`
	`2320`	`+(1 row)`
	`2321`	`+`
	`2322`	`+select count(*) from test_multirange_gist where mr << '{}'::int4multirange;`
	`2323`	`+ count`
	`2324`	`+-------`
	`2325`	`+ 0`
	`2326`	`+(1 row)`
	`2327`	`+`
	`2328`	`+select count(*) from test_multirange_gist where mr >> '{}'::int4multirange;`
	`2329`	`+ count`
	`2330`	`+-------`
	`2331`	`+ 0`
	`2332`	`+(1 row)`
	`2333`	`+`
	`2334`	`+select count(*) from test_multirange_gist where mr &< '{}'::int4multirange;`
	`2335`	`+ count`
	`2336`	`+-------`
	`2337`	`+ 0`
	`2338`	`+(1 row)`
	`2339`	`+`
	`2340`	`+select count(*) from test_multirange_gist where mr &> '{}'::int4multirange;`
	`2341`	`+ count`
	`2342`	`+-------`
	`2343`	`+ 0`
	`2344`	`+(1 row)`
	`2345`	`+`
	`2346`	`+select count(*) from test_multirange_gist where mr -\|- '{}'::int4multirange;`
	`2347`	`+ count`
	`2348`	`+-------`
	`2349`	`+ 0`
	`2350`	`+(1 row)`
	`2351`	`+`
`2250`	`2352`	`select count(*) from test_multirange_gist where mr = int4multirange(int4range(10,20), int4range(30,40), int4range(50,60));`
`2251`	`2353`	`count`
`2252`	`2354`	`-------`
`@@ -2365,6 +2467,114 @@ select count(*) from test_multirange_gist where mr -\|- int4multirange(int4range(`
`2365`	`2467`	`SET enable_seqscan = f;`
`2366`	`2468`	`SET enable_indexscan = t;`
`2367`	`2469`	`SET enable_bitmapscan = f;`
	`2470`	`+select count(*) from test_multirange_gist where mr = '{}'::int4multirange;`
	`2471`	`+ count`
	`2472`	`+-------`
	`2473`	`+ 500`
	`2474`	`+(1 row)`
	`2475`	`+`
	`2476`	`+select count(*) from test_multirange_gist where mr @> 'empty'::int4range;`
	`2477`	`+ count`
	`2478`	`+-------`
	`2479`	`+ 3700`
	`2480`	`+(1 row)`
	`2481`	`+`
	`2482`	`+select count(*) from test_multirange_gist where mr && 'empty'::int4range;`
	`2483`	`+ count`
	`2484`	`+-------`
	`2485`	`+ 0`
	`2486`	`+(1 row)`
	`2487`	`+`
	`2488`	`+select count(*) from test_multirange_gist where mr <@ 'empty'::int4range;`
	`2489`	`+ count`
	`2490`	`+-------`
	`2491`	`+ 500`
	`2492`	`+(1 row)`
	`2493`	`+`
	`2494`	`+select count(*) from test_multirange_gist where mr << 'empty'::int4range;`
	`2495`	`+ count`
	`2496`	`+-------`
	`2497`	`+ 0`
	`2498`	`+(1 row)`
	`2499`	`+`
	`2500`	`+select count(*) from test_multirange_gist where mr >> 'empty'::int4range;`
	`2501`	`+ count`
	`2502`	`+-------`
	`2503`	`+ 0`
	`2504`	`+(1 row)`
	`2505`	`+`
	`2506`	`+select count(*) from test_multirange_gist where mr &< 'empty'::int4range;`
	`2507`	`+ count`
	`2508`	`+-------`
	`2509`	`+ 0`
	`2510`	`+(1 row)`
	`2511`	`+`
	`2512`	`+select count(*) from test_multirange_gist where mr &> 'empty'::int4range;`
	`2513`	`+ count`
	`2514`	`+-------`
	`2515`	`+ 0`
	`2516`	`+(1 row)`
	`2517`	`+`
	`2518`	`+select count(*) from test_multirange_gist where mr -\|- 'empty'::int4range;`
	`2519`	`+ count`
	`2520`	`+-------`
	`2521`	`+ 0`
	`2522`	`+(1 row)`
	`2523`	`+`
	`2524`	`+select count(*) from test_multirange_gist where mr @> '{}'::int4multirange;`
	`2525`	`+ count`
	`2526`	`+-------`
	`2527`	`+ 3700`
	`2528`	`+(1 row)`
	`2529`	`+`
	`2530`	`+select count(*) from test_multirange_gist where mr @> '{}'::int4multirange;`
	`2531`	`+ count`
	`2532`	`+-------`
	`2533`	`+ 3700`
	`2534`	`+(1 row)`
	`2535`	`+`
	`2536`	`+select count(*) from test_multirange_gist where mr && '{}'::int4multirange;`
	`2537`	`+ count`
	`2538`	`+-------`
	`2539`	`+ 0`
	`2540`	`+(1 row)`
	`2541`	`+`
	`2542`	`+select count(*) from test_multirange_gist where mr <@ '{}'::int4multirange;`
	`2543`	`+ count`
	`2544`	`+-------`
	`2545`	`+ 500`
	`2546`	`+(1 row)`
	`2547`	`+`
	`2548`	`+select count(*) from test_multirange_gist where mr << '{}'::int4multirange;`
	`2549`	`+ count`
	`2550`	`+-------`
	`2551`	`+ 0`
	`2552`	`+(1 row)`
	`2553`	`+`
	`2554`	`+select count(*) from test_multirange_gist where mr >> '{}'::int4multirange;`
	`2555`	`+ count`
	`2556`	`+-------`
	`2557`	`+ 0`
	`2558`	`+(1 row)`
	`2559`	`+`
	`2560`	`+select count(*) from test_multirange_gist where mr &< '{}'::int4multirange;`
	`2561`	`+ count`
	`2562`	`+-------`
	`2563`	`+ 0`
	`2564`	`+(1 row)`
	`2565`	`+`
	`2566`	`+select count(*) from test_multirange_gist where mr &> '{}'::int4multirange;`
	`2567`	`+ count`
	`2568`	`+-------`
	`2569`	`+ 0`
	`2570`	`+(1 row)`
	`2571`	`+`
	`2572`	`+select count(*) from test_multirange_gist where mr -\|- '{}'::int4multirange;`
	`2573`	`+ count`
	`2574`	`+-------`
	`2575`	`+ 0`
	`2576`	`+(1 row)`
	`2577`	`+`
`2368`	`2578`	`select count(*) from test_multirange_gist where mr @> 'empty'::int4range;`
`2369`	`2579`	`count`
`2370`	`2580`	`-------`

0 commit comments

Comments

(0)

Movatterモバイル変換

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Commit178ec46

File tree

3 files changed

3 files changed

`‎src/backend/utils/adt/multirangetypes_selfuncs.c`

`‎src/test/regress/expected/multirangetypes.out`

0 commit comments