Re: SP-GiST support for inet datatypes

<div dir="ltr"><br /><div class="gmail_extra"><br /><div class="gmail_quote">On Thu, Mar 3, 2016 at 11:45 AM, Emre
Hasegeli<span dir="ltr"><<a href="mailto:emre@hasegeli.com" target="_blank">emre@hasegeli.com</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid
rgb(204,204,204);padding-left:1ex"><spanclass="">> Emre, I checked original thread and didn't find sample data.
Couldyou provide them for testing ?<br /><br /></span>I found it on the Git history:<br /><br /><a
href="https://github.com/job/irrexplorer/blob/9e8b5330d7ef0022abbe1af18291257e044eb24b/data/irrexplorer_dump.sql.gz?raw=true"
rel="noreferrer"
target="_blank">https://github.com/job/irrexplorer/blob/9e8b5330d7ef0022abbe1af18291257e044eb24b/data/irrexplorer_dump.sql.gz?raw=true</a><br
/></blockquote></div><br/></div><div class="gmail_extra">Thanks !<br /><br /></div><div class="gmail_extra">spgist
indexcreates 2 times faster than gist, but index size is noticeably  bugger <br /><br />\di+ route_*<br
/>                           List of relations<br /> Schema |     Name     | Type  |  Owner   | Table  |  Size  |
Description<br/>--------+--------------+-------+----------+--------+--------+-------------<br /> public | route_gist  
|index | postgres | routes | 96 MB  |<br /> public | route_spgist | index | postgres | routes | 132 MB |<br />(2
rows)<br/><br /></div><div class="gmail_extra">Spgist index tree is much better  than gist - 12149 pages vs 1334760
!<br/></div><div class="gmail_extra"><br /></div><div class="gmail_extra"><br /><br />EXPLAIN (ANALYZE, buffers) SELECT
routes.routeFROM routes JOIN hmm ON<br />routes.route && hmm.route;<br
/>                                                              QUERY PLAN<br
/>----------------------------------------------------------------------------------------------------------------------------------------<br
/> NestedLoop  (cost=0.41..570430.27 rows=2338 width=7) (actual time=5.730..12085.747 rows=8127 loops=1)<br />  
Buffers:shared hit=1334760<br />   ->  Seq Scan on hmm  (cost=0.00..11.32 rows=732 width=7) (actual
time=0.013..0.528rows=732 loops=1)<br />         Buffers: shared hit=4<br />   ->  Index Only Scan using route_gist
onroutes  (cost=0.41..550.26 rows=22900 width=7) (actual time=2.491..16.503 rows=11 loops=732)<br />         Index
Cond:(route && (hmm.route)::inet)<br />         Heap Fetches: 8127<br />         Buffers: shared hit=1334756<br
/> Planningtime: 0.827 ms<br /> Execution time: 12086.513 ms<br />(10 rows)<br /><br />EXPLAIN (ANALYZE, buffers)
SELECTroutes.route FROM routes JOIN hmm ON<br />routes.route && hmm.route;<br
/>                                                              QUERY PLAN<br
/>-----------------------------------------------------------------------------------------------------------------------------------------<br
/> NestedLoop  (cost=0.41..588634.27 rows=2338 width=7) (actual time=0.043..12.150 rows=8127 loops=1)<br />   Buffers:
sharedhit=12149<br />   ->  Seq Scan on hmm  (cost=0.00..11.32 rows=732 width=7) (actual time=0.013..0.075 rows=732
loops=1)<br/>         Buffers: shared hit=4<br />   ->  Index Only Scan using route_spgist on routes 
(cost=0.41..575.13rows=22900 width=7) (actual time=0.011..0.015 rows=11 loops=732)<br />         Index Cond: (route
&&(hmm.route)::inet)<br />         Heap Fetches: 8127<br />         Buffers: shared hit=12145<br /> Planning
time:0.779 ms<br /> Execution time: 12.603 ms<br />(10 rows)<br /></div><div class="gmail_extra"><br /><br /></div><div
class="gmail_extra"><br/><br /></div></div>