On Tue, Mar 8, 2016 at 11:17 PM, Oleg Bartunov <obartu...@gmail.com> wrote:
> > > On Thu, Mar 3, 2016 at 11:45 AM, Emre Hasegeli <e...@hasegeli.com> wrote: > >> > Emre, I checked original thread and didn't find sample data. Could you >> provide them for testing ? >> >> I found it on the Git history: >> >> >> https://github.com/job/irrexplorer/blob/9e8b5330d7ef0022abbe1af18291257e044eb24b/data/irrexplorer_dump.sql.gz?raw=true >> > > Thanks ! > > spgist index creates 2 times faster than gist, but index size is > noticeably bugger > > \di+ route_* > List of relations > Schema | Name | Type | Owner | Table | Size | Description > --------+--------------+-------+----------+--------+--------+------------- > public | route_gist | index | postgres | routes | 96 MB | > public | route_spgist | index | postgres | routes | 132 MB | > (2 rows) > > Spgist index tree is much better than gist - 12149 pages vs 1334760 ! > I also noticed, that spgist is much faster than gist for other inet operators. I'd like to see in 9.6. > > > > EXPLAIN (ANALYZE, buffers) SELECT routes.route FROM routes JOIN hmm ON > routes.route && hmm.route; > QUERY PLAN > > ---------------------------------------------------------------------------------------------------------------------------------------- > Nested Loop (cost=0.41..570430.27 rows=2338 width=7) (actual > time=5.730..12085.747 rows=8127 loops=1) > Buffers: shared hit=1334760 > -> Seq Scan on hmm (cost=0.00..11.32 rows=732 width=7) (actual > time=0.013..0.528 rows=732 loops=1) > Buffers: shared hit=4 > -> Index Only Scan using route_gist on routes (cost=0.41..550.26 > rows=22900 width=7) (actual time=2.491..16.503 rows=11 loops=732) > Index Cond: (route && (hmm.route)::inet) > Heap Fetches: 8127 > Buffers: shared hit=1334756 > Planning time: 0.827 ms > Execution time: 12086.513 ms > (10 rows) > > EXPLAIN (ANALYZE, buffers) SELECT routes.route FROM routes JOIN hmm ON > routes.route && hmm.route; > QUERY PLAN > > ----------------------------------------------------------------------------------------------------------------------------------------- > Nested Loop (cost=0.41..588634.27 rows=2338 width=7) (actual > time=0.043..12.150 rows=8127 loops=1) > Buffers: shared hit=12149 > -> Seq Scan on hmm (cost=0.00..11.32 rows=732 width=7) (actual > time=0.013..0.075 rows=732 loops=1) > Buffers: shared hit=4 > -> Index Only Scan using route_spgist on routes (cost=0.41..575.13 > rows=22900 width=7) (actual time=0.011..0.015 rows=11 loops=732) > Index Cond: (route && (hmm.route)::inet) > Heap Fetches: 8127 > Buffers: shared hit=12145 > Planning time: 0.779 ms > Execution time: 12.603 ms > (10 rows) > > > > >
