On Wed, Mar 2, 2016 at 11:56 PM, Emre Hasegeli <e...@hasegeli.com> wrote:
> Attached patches add SP-GiST support to the inet datatypes. The operator > class comes with a small change on the SP-GiST framework to allow fixed > number of child nodes. > > The index is like prefix tree except that it doesn't bother to split the > addresses into parts as text is split. It also doesn't use labels to know > the part after the prefix, but relies on static node numbers. > > Thanks, Emre for interesting spgist. We are bit busy and will take a look on your patches when come to our spgist patch. > The GiST index released with version 9.4 performs really bad with real > world data. SP-GiST works much better with the query posted to the > performance list [1] a while ago: > > > hasegeli=# SELECT DISTINCT route INTO hmm FROM routes_view WHERE asn = > 2914; > > SELECT 732 > > > > hasegeli=# EXPLAIN ANALYZE SELECT routes.route FROM routes JOIN hmm ON > routes.route && hmm.route; > > QUERY PLAN > > > ---------------------------------------------------------------------------------------------------------------------------------------- > > Nested Loop (cost=0.41..571742.27 rows=2248 width=7) (actual > time=12.643..20474.813 rows=8127 loops=1) > > -> Seq Scan on hmm (cost=0.00..11.32 rows=732 width=7) (actual > time=0.017..0.524 rows=732 loops=1) > > -> Index Only Scan using route_gist on routes (cost=0.41..552.05 > rows=22900 width=7) (actual time=4.851..27.948 rows=11 loops=732) > > Index Cond: (route && (hmm.route)::inet) > > Heap Fetches: 8127 > > Planning time: 1.507 ms > > Execution time: 20475.605 ms > > (7 rows) > > > > hasegeli=# DROP INDEX route_gist; > > DROP INDEX > > > > hasegeli=# CREATE INDEX route_spgist ON routes USING spgist (route); > > CREATE INDEX > > > > hasegeli=# EXPLAIN ANALYZE SELECT routes.route FROM routes JOIN hmm ON > routes.route && hmm.route; > > QUERY PLAN > > > ----------------------------------------------------------------------------------------------------------------------------------------- > > Nested Loop (cost=0.41..588634.27 rows=2248 width=7) (actual > time=0.081..16.961 rows=8127 loops=1) > > -> Seq Scan on hmm (cost=0.00..11.32 rows=732 width=7) (actual > time=0.022..0.079 rows=732 loops=1) > > -> Index Only Scan using route_spgist on routes (cost=0.41..575.13 > rows=22900 width=7) (actual time=0.014..0.021 rows=11 loops=732) > > Index Cond: (route && (hmm.route)::inet) > > Heap Fetches: 8127 > > Planning time: 1.376 ms > > Execution time: 15.936 ms > > [1] > http://www.postgresql.org/message-id/flat/alpine.DEB.2.11.1508251504160.31004@pyrite#alpine.DEB.2.11.1508251504160.31004@pyrite > > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > >
