Re: SP-GiST support for inet datatypes
От | Oleg Bartunov |
---|---|
Тема | Re: SP-GiST support for inet datatypes |
Дата | |
Msg-id | CAF4Au4zWu0-Jpx1zXoe0E8RyFtyywaA1RWM3tGsDtDqSqUcZow@mail.gmail.com обсуждение исходный текст |
Ответ на | Re: SP-GiST support for inet datatypes (Oleg Bartunov <obartunov@gmail.com>) |
Ответы |
Re: SP-GiST support for inet datatypes
(Emre Hasegeli <emre@hasegeli.com>)
|
Список | pgsql-hackers |
On Tue, Mar 8, 2016 at 11:17 PM, Oleg Bartunov <obartunov@gmail.com> wrote:
On Thu, Mar 3, 2016 at 11:45 AM, Emre Hasegeli <emre@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=trueThanks !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)
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Robert HaasДата:
Сообщение: Re: Add generate_series(date,date) and generate_series(date,date,integer)