On Wed, Aug 26, 2015 at 4:29 AM, Henrik Thostrup Jensen <htj@nordu.net> wrote:
On Wed, 26 Aug 2015, Emre Hasegeli wrote:
Are the coverage operatons just that expensive?
They shouldn't be. A similar query like yours works in 0.5 second on my laptop:
[snip]
I get the same from your testcase.
Maybe, something we haven't expected about your dataset causes a performance regression on the index. Did you see anything relevant on the server logs on index creation time?
I tried dropping and re-creating the index. The only log entry was for the drop statement.
The distribution of the data is not uniform like the data set you produce. Though I find it hard to believe that it would affect this as much.
select masklen(route), count(*) from routes group by masklen(route);
Any chance you can share the actual underlying data? I noticed it wasn't on github, but is that because it is proprietary, or just because you don't think it is interesting?
irrexplorer=> explain analyze select routes.route from routes join hmm on routes.route && hmm.route; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.41..511914.27 rows=2558 width=7) (actual time=8.096..17209.778 rows=8127 loops=1) -> Seq Scan on hmm (cost=0.00..11.32 rows=732 width=7) (actual time=0.010..0.609 rows=732 loops=1) -> Index Only Scan using route_gist on routes (cost=0.41..470.32 rows=22900 width=7) (actual time=4.823..23.502 rows=11 loops=732) Index Cond: (route && (hmm.route)::inet) Heap Fetches: 0 Planning time: 0.971 ms Execution time: 17210.627 ms (7 rows)
If you loop over the 732 rows yourself, issuing the simple query against each retrieved constant value:
explain (analyze,buffers) select routes.route from routes where route && $1
Does each one take about the same amount of time, or are there some outlier values which take much more time than the others?