|
| 1 | +-- spoint_dwithin function selectivity |
| 2 | +set jit = off; -- suppress extra planning output |
| 3 | +select explain('select * from spoint10k where spoint_dwithin(star, spoint(1,1), 1)'); |
| 4 | + explain |
| 5 | +-------------------------------------------------------------------------------------------------- |
| 6 | + Bitmap Heap Scan on spoint10k (rows=2298 width=16) (actual rows=3009.00 loops=1) |
| 7 | + Filter: spoint_dwithin(star, '(1 , 1)'::spoint, '1'::double precision) |
| 8 | + Rows Removed by Filter: 1560 |
| 9 | + Heap Blocks: exact=55 |
| 10 | + -> Bitmap Index Scan on spoint10k_star_idx (rows=2298 width=0) (actual rows=4569.00 loops=1) |
| 11 | + Index Cond: (star <@ '<(1 , 1) , 1>'::scircle) |
| 12 | +(6 rows) |
| 13 | + |
| 14 | +select explain('select * from spoint10k where spoint_dwithin(star, spoint(1,1), .1)'); |
| 15 | + explain |
| 16 | +---------------------------------------------------------------------------------------------- |
| 17 | + Bitmap Heap Scan on spoint10k (rows=25 width=16) (actual rows=29.00 loops=1) |
| 18 | + Filter: spoint_dwithin(star, '(1 , 1)'::spoint, '0.1'::double precision) |
| 19 | + Rows Removed by Filter: 19 |
| 20 | + Heap Blocks: exact=32 |
| 21 | + -> Bitmap Index Scan on spoint10k_star_idx (rows=25 width=0) (actual rows=48.00 loops=1) |
| 22 | + Index Cond: (star <@ '<(1 , 1) , 0.1>'::scircle) |
| 23 | +(6 rows) |
| 24 | + |
| 25 | +select explain('select * from spoint10k where spoint_dwithin(star, spoint(1,1), .01)'); |
| 26 | + explain |
| 27 | +------------------------------------------------------------------------------------------------ |
| 28 | + Index Scan using spoint10k_star_idx on spoint10k (rows=1 width=16) (actual rows=1.00 loops=1) |
| 29 | + Index Cond: (star <@ '<(1 , 1) , 0.01>'::scircle) |
| 30 | +(2 rows) |
| 31 | + |
| 32 | +select explain('select * from spoint10k where spoint_dwithin(spoint(1,1), star, 1)'); |
| 33 | + explain |
| 34 | +-------------------------------------------------------------------------------------------------- |
| 35 | + Bitmap Heap Scan on spoint10k (rows=2298 width=16) (actual rows=3009.00 loops=1) |
| 36 | + Filter: spoint_dwithin('(1 , 1)'::spoint, star, '1'::double precision) |
| 37 | + Rows Removed by Filter: 1560 |
| 38 | + Heap Blocks: exact=55 |
| 39 | + -> Bitmap Index Scan on spoint10k_star_idx (rows=2298 width=0) (actual rows=4569.00 loops=1) |
| 40 | + Index Cond: (star <@ '<(1 , 1) , 1>'::scircle) |
| 41 | +(6 rows) |
| 42 | + |
| 43 | +select explain('select * from spoint10k where spoint_dwithin(spoint(1,1), star, .1)'); |
| 44 | + explain |
| 45 | +---------------------------------------------------------------------------------------------- |
| 46 | + Bitmap Heap Scan on spoint10k (rows=25 width=16) (actual rows=29.00 loops=1) |
| 47 | + Filter: spoint_dwithin('(1 , 1)'::spoint, star, '0.1'::double precision) |
| 48 | + Rows Removed by Filter: 19 |
| 49 | + Heap Blocks: exact=32 |
| 50 | + -> Bitmap Index Scan on spoint10k_star_idx (rows=25 width=0) (actual rows=48.00 loops=1) |
| 51 | + Index Cond: (star <@ '<(1 , 1) , 0.1>'::scircle) |
| 52 | +(6 rows) |
| 53 | + |
| 54 | +select explain('select * from spoint10k where spoint_dwithin(spoint(1,1), star, .01)'); |
| 55 | + explain |
| 56 | +------------------------------------------------------------------------------------------------ |
| 57 | + Index Scan using spoint10k_star_idx on spoint10k (rows=1 width=16) (actual rows=1.00 loops=1) |
| 58 | + Index Cond: (star <@ '<(1 , 1) , 0.01>'::scircle) |
| 59 | +(2 rows) |
| 60 | + |
| 61 | +select explain('select * from spoint10k a join spoint10k b on spoint_dwithin(a.star, b.star, 1)', do_analyze := 'false'); |
| 62 | + explain |
| 63 | +--------------------------------------------------------------------------------------- |
| 64 | + Nested Loop (rows=22984885 width=32) |
| 65 | + -> Seq Scan on spoint10k a (rows=10000 width=16) |
| 66 | + -> Index Scan using spoint10k_star_idx on spoint10k b (rows=2298 width=16) |
| 67 | + Index Cond: (star OPERATOR(public.<@) scircle(a.star, '1'::double precision)) |
| 68 | +(4 rows) |
| 69 | + |
| 70 | +select explain('select * from spoint10k a join spoint10k b on spoint_dwithin(a.star, b.star, .1)'); |
| 71 | + explain |
| 72 | +-------------------------------------------------------------------------------------------------------------- |
| 73 | + Nested Loop (rows=249792 width=32) (actual rows=505342.00 loops=1) |
| 74 | + -> Seq Scan on spoint10k a (rows=10000 width=16) (actual rows=10000.00 loops=1) |
| 75 | + -> Index Scan using spoint10k_star_idx on spoint10k b (rows=25 width=16) (actual rows=50.53 loops=10000) |
| 76 | + Index Cond: (star OPERATOR(public.<@) scircle(a.star, '0.1'::double precision)) |
| 77 | + Rows Removed by Index Recheck: 31 |
| 78 | +(5 rows) |
| 79 | + |
| 80 | +select explain('select * from spoint10k a join spoint10k b on spoint_dwithin(a.star, b.star, .01)'); |
| 81 | + explain |
| 82 | +------------------------------------------------------------------------------------------------------------ |
| 83 | + Nested Loop (rows=2500 width=32) (actual rows=17614.00 loops=1) |
| 84 | + -> Seq Scan on spoint10k a (rows=10000 width=16) (actual rows=10000.00 loops=1) |
| 85 | + -> Index Scan using spoint10k_star_idx on spoint10k b (rows=1 width=16) (actual rows=1.76 loops=10000) |
| 86 | + Index Cond: (star OPERATOR(public.<@) scircle(a.star, '0.01'::double precision)) |
| 87 | + Rows Removed by Index Recheck: 1 |
| 88 | +(5 rows) |
| 89 | + |
| 90 | +-- spoint_dwithin is symmetric in the first two arguments |
| 91 | +select explain('select * from spoint10k a join spoint10k b on spoint_dwithin(a.star, b.star, .01) |
| 92 | + where spoint_dwithin(a.star, spoint(1,1), .1)'); |
| 93 | + explain |
| 94 | +--------------------------------------------------------------------------------------------------------- |
| 95 | + Nested Loop (rows=6 width=32) (actual rows=33.00 loops=1) |
| 96 | + -> Bitmap Heap Scan on spoint10k a (rows=25 width=16) (actual rows=29.00 loops=1) |
| 97 | + Filter: spoint_dwithin(star, '(1 , 1)'::spoint, '0.1'::double precision) |
| 98 | + Rows Removed by Filter: 19 |
| 99 | + Heap Blocks: exact=32 |
| 100 | + -> Bitmap Index Scan on spoint10k_star_idx (rows=25 width=0) (actual rows=48.00 loops=1) |
| 101 | + Index Cond: (star <@ '<(1 , 1) , 0.1>'::scircle) |
| 102 | + -> Index Scan using spoint10k_star_idx on spoint10k b (rows=1 width=16) (actual rows=1.14 loops=29) |
| 103 | + Index Cond: (star OPERATOR(public.<@) scircle(a.star, '0.01'::double precision)) |
| 104 | + Rows Removed by Index Recheck: 0 |
| 105 | +(10 rows) |
| 106 | + |
| 107 | +select explain('select * from spoint10k a join spoint10k b on spoint_dwithin(b.star, a.star, .01) |
| 108 | + where spoint_dwithin(a.star, spoint(1,1), .1)'); |
| 109 | + explain |
| 110 | +--------------------------------------------------------------------------------------------------------- |
| 111 | + Nested Loop (rows=6 width=32) (actual rows=33.00 loops=1) |
| 112 | + -> Bitmap Heap Scan on spoint10k a (rows=25 width=16) (actual rows=29.00 loops=1) |
| 113 | + Filter: spoint_dwithin(star, '(1 , 1)'::spoint, '0.1'::double precision) |
| 114 | + Rows Removed by Filter: 19 |
| 115 | + Heap Blocks: exact=32 |
| 116 | + -> Bitmap Index Scan on spoint10k_star_idx (rows=25 width=0) (actual rows=48.00 loops=1) |
| 117 | + Index Cond: (star <@ '<(1 , 1) , 0.1>'::scircle) |
| 118 | + -> Index Scan using spoint10k_star_idx on spoint10k b (rows=1 width=16) (actual rows=1.14 loops=29) |
| 119 | + Index Cond: (star OPERATOR(public.<@) scircle(a.star, '0.01'::double precision)) |
| 120 | + Rows Removed by Index Recheck: 0 |
| 121 | +(10 rows) |
| 122 | + |
| 123 | +-- both sides indexable, check if the planner figures out the better choice |
| 124 | +select explain('select * from spoint10k a join spoint10k b on spoint_dwithin(a.star, b.star, .01) |
| 125 | + where spoint_dwithin(a.star, spoint(1,1), .1) and spoint_dwithin(b.star, spoint(1,1), .05)'); |
| 126 | + explain |
| 127 | +------------------------------------------------------------------------------------------------------------------------------------- |
| 128 | + Nested Loop (rows=1 width=32) (actual rows=16.00 loops=1) |
| 129 | + -> Bitmap Heap Scan on spoint10k b (rows=6 width=16) (actual rows=12.00 loops=1) |
| 130 | + Filter: spoint_dwithin(star, '(1 , 1)'::spoint, '0.05'::double precision) |
| 131 | + Rows Removed by Filter: 4 |
| 132 | + Heap Blocks: exact=14 |
| 133 | + -> Bitmap Index Scan on spoint10k_star_idx (rows=6 width=0) (actual rows=16.00 loops=1) |
| 134 | + Index Cond: (star <@ '<(1 , 1) , 0.05>'::scircle) |
| 135 | + -> Index Scan using spoint10k_star_idx on spoint10k a (rows=1 width=16) (actual rows=1.33 loops=12) |
| 136 | + Index Cond: ((star OPERATOR(public.<@) scircle(b.star, '0.01'::double precision)) AND (star <@ '<(1 , 1) , 0.1>'::scircle)) |
| 137 | + Rows Removed by Index Recheck: 0 |
| 138 | +(10 rows) |
| 139 | + |
| 140 | +select explain('select * from spoint10k a join spoint10k b on spoint_dwithin(a.star, b.star, .01) |
| 141 | + where spoint_dwithin(a.star, spoint(1,1), .05) and spoint_dwithin(b.star, spoint(1,1), .1)'); |
| 142 | + explain |
| 143 | +------------------------------------------------------------------------------------------------------------------------------------- |
| 144 | + Nested Loop (rows=1 width=32) (actual rows=16.00 loops=1) |
| 145 | + -> Bitmap Heap Scan on spoint10k a (rows=6 width=16) (actual rows=12.00 loops=1) |
| 146 | + Filter: spoint_dwithin(star, '(1 , 1)'::spoint, '0.05'::double precision) |
| 147 | + Rows Removed by Filter: 4 |
| 148 | + Heap Blocks: exact=14 |
| 149 | + -> Bitmap Index Scan on spoint10k_star_idx (rows=6 width=0) (actual rows=16.00 loops=1) |
| 150 | + Index Cond: (star <@ '<(1 , 1) , 0.05>'::scircle) |
| 151 | + -> Index Scan using spoint10k_star_idx on spoint10k b (rows=1 width=16) (actual rows=1.33 loops=12) |
| 152 | + Index Cond: ((star OPERATOR(public.<@) scircle(a.star, '0.01'::double precision)) AND (star <@ '<(1 , 1) , 0.1>'::scircle)) |
| 153 | + Rows Removed by Index Recheck: 0 |
| 154 | +(10 rows) |
| 155 | + |