<div dir="ltr">Hi Dan,<br><div><div class="gmail_extra"><br><div class="gmail_quote">2017-07-26 19:15 GMT+02:00 Dan Lyke <span dir="ltr"><<a href="mailto:danlyke@flutterby.com" target="_blank">danlyke@flutterby.com</a>></span>:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><span class="gmail-">On Wed, 26 Jul 2017 12:31:07 +0200<br>
Giuseppe Broccolo <<a href="mailto:g.broccolo.7@gmail.com">g.broccolo.7@gmail.com</a>> wrote:<br>
> On old dataset, the index is scanned through the bitmap: there are two<br>
> phases, in the first the index is used to build a bitmap, then the<br>
> query bases to find matching entries through the bitmap itself. This<br>
> strategy is generally chosen if the index scan would involve several<br>
> accesses on same blocks of the indexes.<br>
</span>[snip]<br>
<br>
Huh. So a possibility is that somehow the data ended up in disk<br>
proximity in the first database, and is widely spread out in the<br>
second? This assumption based on the query mostly being about:<br>
<br>
geometry &&<br>
ST_SetSRID(ST_MakeBox2D(ST_<wbr>MakePoint(...),ST_MakePoint(..<wbr>.)), 4326)<br>
<br>
(Basically, we're looking for a set of line segments within a bounding<br>
region).<span class="gmail-"><br></span></blockquote><div><br></div><div>It's not related to how data is spread (or less) on disk, but how geometry are spatially<br>distributed. PostgreSQL is smart enough to keep data contiguous. More specifically<br></div><div>in this case, if data has been freshly imported on the DB, data/index blocks are filled<br></div><div>sequentially. <br><br></div><div>What I was wondering about is if there could be a reason of why index scan in now based<br></div><div>on the bitmap, so why it has more to inspect the same blocks through the bitmap.<br><br></div><div>Moreover, if RAM resource is enough to contain the data in memory, blocks distribution<br></div><div>on disk should not be a problem (anyway, since we are talking about this, could you provide<br></div><div>the ratio datasetsize/RAMresource?).<br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><span class="gmail-"></span>The datasets are just two subsequent quarters of a combination of HERE<br>
and OpenStreetMap data. This particular table is just HERE road<br>
segments. The import process in both cases should be the same.<br>
<br>
One weirdness that I'm trying to figure out how to quantify: This<br>
particular table in the quarter's data is 33GB, the new quarter's<br>
data is 70GB[1], and I'm trying to figure out why. The geometry as<br>
ST_AsText is 14G in the old quarter, new one is 16G, so not a huge<br>
change there. Similar difference in number of rows (< 20%, not<br>
enough to account for >2x data size...<br>
<br>
[1] SELECT relname, relpages, reltuples::numeric,<br>
pg_size_pretty(pg_table_size(<wbr>oid))<br>
FROM pg_class WHERE oid='...'::regclass;<span class="gmail-"><br></span></blockquote><div><br></div><div>Could this be related to more metadata present in the new dataset (e.g. more<br>attributes per record)? I actually don't know at all HERE datasets.<br><br></div><div>If I correctly understood, the new dataset includes 16GB of geospatial data instead<br></div><div>of the 14GB of the old dataset? Well, ~14% of more data shouldn't make change<br></div><div>planner's strategy...but a larger amount of metadata could justify a significant spread<br></div><div>of geospatial data in an higher amount of blocks on disk, so the change in the strategy.<br></div><div> </div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><span class="gmail-"></span>Here's EXPLAIN ANALYZE, old one :<br>
<br>
Sort (cost=6802.93..6806.30 rows=1346 width=128) (actual time=132.710..133.295 rows=477 loops=1)<br>
Sort Key: (st_distance_sphere('...'::<wbr>geometry, st_geometryn(way, 1))), osm_id<br>
Sort Method: quicksort Memory: 62kB<br>
-> Bitmap Heap Scan on line (cost=50.85..6732.98 rows=1346 width=128) (actual time=0.976..131.077 rows=477 loops=1)<br>
<span class="gmail-"> Recheck Cond: (way && '...'::geometry)<br>
</span> Heap Blocks: exact=261<br>
-> Bitmap Index Scan on line_index (cost=0.00..50.52 rows=1346 width=0) (actual time=0.418..0.418 rows=477 loops=1)<br>
<span class="gmail-"> Index Cond: (way && '...'::geometry)<br>
</span> Planning time: 15.924 ms<br>
Execution time: 133.925 ms<br>
(10 rows)<br>
<br>
<br>
New one:<br>
<br>
Sort (cost=2595.88..2597.16 rows=509 width=127) (actual time=220.955..221.449 rows=505 loops=1)<br>
Sort Key: (st_distance_sphere('...'::<wbr>geometry, st_geometryn(way, 1))), osm_id<br>
Sort Method: quicksort Memory: 64kB<br>
-> Index Scan using line_index on line (cost=0.55..2573.00 rows=509 width=127) (actual time=3.407..219.551 rows=505 loops=1)<br>
<span class="gmail-"> Index Cond: (way && '...'::geometry)<br>
</span> Planning time: 0.469 ms<br>
Execution time: 222.014 ms<br>
(7 rows)<br></blockquote><div><br></div><div>Well, the number of matched rows looks to be almost the same in both cases (505 vs. 477) confirming that the geospatial content in<br></div><div>the two version of the dataset has small changes/new entries. This could be confirmed comparing indexes size on the two datasets.<br><br></div><div>Most of the time in the new dataset (3.407..219.551 ms) is spent in the index scan, a larger amount of time if compared with the index scan<br></div><div>needed to build and scan the bitmap (0.418..131.077 ms). This because index scan strategy (differently from index only scan one) needs to inspects table blocks too for visibility of rows, and seems that lot of blocks (2573) are inspected in the new dataset during index scan. <br><br></div><div>Anyway, probably the index scan is the proper plan chosen by the planner (you can check this turning off index scan plan, and see an higher execution time in case the bitmap index/heap scan is used in query execution on the new dataset): check about why new dataset is so large (70GB vs. 33GB), this could be the real reason of the more time needed for query execution.<br><br></div><div>Hope this helps again :)<br></div><div>Giuseppe.<br></div></div></div></div></div>