Optimizer choosing slower strategy (Bitmap Heap vs Index)?

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Optimizer choosing slower strategy (Bitmap Heap vs Index)?

Dan Lyke
I'm trying to figure out a performance issue between two quarters of
map data. Both are using PosgreSQL 9.5.4 on Amazon RDS. The current
quarter's data appears to be much slower than the old quarter's data.

VACUUM ANALYZE has been run.

EXPLAIN ... on the old data is doing a:

->  Bitmap Heap Scan on foo_line (cost=50.85..5380.25 rows=1346
width=306)  
    Recheck Cond: (way && '...'::geometry)
    Filter: (highway IS NOT NULL)
    ->  Bitmap Index Scan on foo_index  (cost=0.00..50.52 rows=1346
width=0)  
        Index Cond: (way && '...'::geometry)


And on the new data:

->  Index Scan using foo_index on foo_line  (cost=0.55..1346.36  
rows=332 width=304)
    Index Cond: (way &&  '...'::geometry)
    Filter: (highway IS NOT NULL)

Which looks to me like it's doing the filter before the Bitmap Index
Scan?

In either case, the new database is substantially slower than the old
database, and I need to fix that, and I'm down to asking strangers on
the Internet while I continue to Google around for solutions.

Dan




_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Optimizer choosing slower strategy (Bitmap Heap vs Index)?

Giuseppe Broccolo-2
Hi Dan,

2017-07-25 18:47 GMT+02:00 Dan Lyke <[hidden email]>:
I'm trying to figure out a performance issue between two quarters of
map data. Both are using PosgreSQL 9.5.4 on Amazon RDS. The current
quarter's data appears to be much slower than the old quarter's data.

VACUUM ANALYZE has been run.

EXPLAIN ... on the old data is doing a:

->  Bitmap Heap Scan on foo_line (cost=<a href="tel:50.85..5380.25" value="+15085538025">50.85..5380.25 rows=1346
width=306)
    Recheck Cond: (way && '...'::geometry)
    Filter: (highway IS NOT NULL)
    ->  Bitmap Index Scan on foo_index  (cost=0.00..50.52 rows=1346
width=0)
        Index Cond: (way && '...'::geometry)


And on the new data:

->  Index Scan using foo_index on foo_line  (cost=0.55..1346.36
rows=332 width=304)
    Index Cond: (way &&  '...'::geometry)
    Filter: (highway IS NOT NULL)

Which looks to me like it's doing the filter before the Bitmap Index
Scan?

Actually, the planner is choosing a different strategy for query execution.

On old dataset, the index is scanned through the bitmap: there are two
phases, in the first the index is used to build a bitmap, then the query
bases to find matching entries through the bitmap itself. This strategy
is generally chosen if the index scan would involve several accesses
on same blocks of the indexes.

On new dataset, a simple index scan is executed, avoiding the bitmap
index/heap scan. This is generally faster if index blocks are singularly
accessed.

Basing on planner estimations attached in yours EXPLAIN outputs,
this could be indeed the case: on old datasets, the planner estimates
an higher numbers of involved records by the query execution through
the index scan (1346 vs. 332), and an higher cost in terms of inspected
index blocks (5380 vs. 1346), so the bitmap strategy is chosen.
 
In either case, the new database is substantially slower than the old
database, and I need to fix that, and I'm down to asking strangers on
the Internet while I continue to Google around for solutions.

Could you provide further details in what could be changed between
old and new datasets? Is the content significantly changed (bounds of
geometries, etc.)?

Furthermore, it could be useful also to have attached the output of the
EXPLAIN ANALYSE command, in order to compare what the planner
estimates with what it actually do.

Hope this helps,
Giuseppe.

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Optimizer choosing slower strategy (Bitmap Heap vs Index)?

Dan Lyke
Lest it get lost further down: THANK YOU! This gives me some things to
continue searching on:


On Wed, 26 Jul 2017 12:31:07 +0200
Giuseppe Broccolo <[hidden email]> wrote:
> On old dataset, the index is scanned through the bitmap: there are two
> phases, in the first the index is used to build a bitmap, then the
> query bases to find matching entries through the bitmap itself. This
> strategy is generally chosen if the index scan would involve several
> accesses on same blocks of the indexes.
[snip]

Huh. So a possibility is that somehow the data ended up in disk
proximity in the first database, and is widely spread out in the
second? This assumption based on the query mostly being about:

  geometry &&
  ST_SetSRID(ST_MakeBox2D(ST_MakePoint(...),ST_MakePoint(...)), 4326)

(Basically, we're looking for a set of line segments within a bounding
region).

> Could you provide further details in what could be changed between
> old and new datasets? Is the content significantly changed (bounds of
> geometries, etc.)?

The datasets are just two subsequent quarters of a combination of HERE
and OpenStreetMap data. This particular table is just HERE road
segments. The import process in both cases should be the same.

One weirdness that I'm trying to figure out how to quantify: This
particular table in the quarter's data is 33GB, the new quarter's
data is 70GB[1], and I'm trying to figure out why. The geometry as
ST_AsText is 14G in the old quarter, new one is 16G, so not a huge
change there. Similar difference in number of rows (< 20%, not
enough to account for >2x data size...

[1] SELECT relname, relpages, reltuples::numeric,
           pg_size_pretty(pg_table_size(oid))
    FROM pg_class WHERE oid='...'::regclass;

> Furthermore, it could be useful also to have attached the output of
> the EXPLAIN ANALYSE command, in order to compare what the planner
> estimates with what it actually do.

Here's EXPLAIN ANALYZE, old one :

 Sort  (cost=6802.93..6806.30 rows=1346 width=128) (actual time=132.710..133.295 rows=477 loops=1)
   Sort Key: (st_distance_sphere('...'::geometry, st_geometryn(way, 1))), osm_id
   Sort Method: quicksort  Memory: 62kB
   ->  Bitmap Heap Scan on line  (cost=50.85..6732.98 rows=1346 width=128) (actual time=0.976..131.077 rows=477 loops=1)  
         Recheck Cond: (way && '...'::geometry)
         Heap Blocks: exact=261
         ->  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)  
               Index Cond: (way && '...'::geometry)
 Planning time: 15.924 ms
 Execution time: 133.925 ms
(10 rows)


New one:

 Sort  (cost=2595.88..2597.16 rows=509 width=127) (actual time=220.955..221.449 rows=505 loops=1)
   Sort Key: (st_distance_sphere('...'::geometry, st_geometryn(way, 1))), osm_id
   Sort Method: quicksort  Memory: 64kB
   ->  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)  
         Index Cond: (way && '...'::geometry)
 Planning time: 0.469 ms
 Execution time: 222.014 ms
(7 rows)
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Optimizer choosing slower strategy (Bitmap Heap vs Index)?

Giuseppe Broccolo-2
Hi Dan,

2017-07-26 19:15 GMT+02:00 Dan Lyke <[hidden email]>:
On Wed, 26 Jul 2017 12:31:07 +0200
Giuseppe Broccolo <[hidden email]> wrote:
> On old dataset, the index is scanned through the bitmap: there are two
> phases, in the first the index is used to build a bitmap, then the
> query bases to find matching entries through the bitmap itself. This
> strategy is generally chosen if the index scan would involve several
> accesses on same blocks of the indexes.
[snip]

Huh. So a possibility is that somehow the data ended up in disk
proximity in the first database, and is widely spread out in the
second? This assumption based on the query mostly being about:

  geometry &&
  ST_SetSRID(ST_MakeBox2D(ST_MakePoint(...),ST_MakePoint(...)), 4326)

(Basically, we're looking for a set of line segments within a bounding
region).

It's not related to how data is spread (or less) on disk, but how geometry are spatially
distributed. PostgreSQL is smart enough to keep data contiguous. More specifically
in this case, if data has been freshly imported on the DB, data/index blocks are filled
sequentially.

What I was wondering about is if there could be a reason of why index scan in now based
on the bitmap, so why it has more to inspect the same blocks through the bitmap.

Moreover, if RAM resource is enough to contain the data in memory, blocks distribution
on disk should not be a problem (anyway, since we are talking about this, could you provide
the ratio datasetsize/RAMresource?).
 
The datasets are just two subsequent quarters of a combination of HERE
and OpenStreetMap data. This particular table is just HERE road
segments. The import process in both cases should be the same.

One weirdness that I'm trying to figure out how to quantify: This
particular table in the quarter's data is 33GB, the new quarter's
data is 70GB[1], and I'm trying to figure out why. The geometry as
ST_AsText is 14G in the old quarter, new one is 16G, so not a huge
change there. Similar difference in number of rows (< 20%, not
enough to account for >2x data size...

[1] SELECT relname, relpages, reltuples::numeric,
           pg_size_pretty(pg_table_size(oid))
    FROM pg_class WHERE oid='...'::regclass;

Could this be related to more metadata present in the new dataset (e.g. more
attributes per record)? I actually don't know at all HERE datasets.

If I correctly understood, the new dataset includes 16GB of geospatial data instead
of the 14GB of the old dataset? Well, ~14% of more data shouldn't make change
planner's strategy...but a larger amount of metadata could justify a significant spread
of geospatial data in an higher amount of blocks on disk, so the change in the strategy.
 
Here's EXPLAIN ANALYZE, old one :

 Sort  (cost=6802.93..6806.30 rows=1346 width=128) (actual time=132.710..133.295 rows=477 loops=1)
   Sort Key: (st_distance_sphere('...'::geometry, st_geometryn(way, 1))), osm_id
   Sort Method: quicksort  Memory: 62kB
   ->  Bitmap Heap Scan on line  (cost=50.85..6732.98 rows=1346 width=128) (actual time=0.976..131.077 rows=477 loops=1)
         Recheck Cond: (way && '...'::geometry)
         Heap Blocks: exact=261
         ->  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)
               Index Cond: (way && '...'::geometry)
 Planning time: 15.924 ms
 Execution time: 133.925 ms
(10 rows)


New one:

 Sort  (cost=2595.88..2597.16 rows=509 width=127) (actual time=220.955..221.449 rows=505 loops=1)
   Sort Key: (st_distance_sphere('...'::geometry, st_geometryn(way, 1))), osm_id
   Sort Method: quicksort  Memory: 64kB
   ->  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)
         Index Cond: (way && '...'::geometry)
 Planning time: 0.469 ms
 Execution time: 222.014 ms
(7 rows)

Well, the number of matched rows looks to be almost the same in both cases (505 vs. 477) confirming that the geospatial content in
the two version of the dataset has small changes/new entries. This could be confirmed comparing indexes size on the two datasets.

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
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.

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.

Hope this helps again :)
Giuseppe.

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Optimizer choosing slower strategy (Bitmap Heap vs Index)?

Dan Lyke
On Thu, 27 Jul 2017 09:33:03 +0200
Giuseppe Broccolo <[hidden email]> wrote:
> Moreover, if RAM resource is enough to contain the data in memory,
> blocks distribution
> on disk should not be a problem (anyway, since we are talking about
> this, could you provide
> the ratio datasetsize/RAMresource?).

So over this weekend I exported the entire database out and imported it
into a new instance. Now that one table size is down to 34GB (from
70GB before), which seems more in-line with the earlier database version
(I guess something happened with extraneous data on import that got
deleted).

But the problem persists.

This main table is 34G, we've got 3 other tables we use intensively, one
is about 3.4G, the other two are 200M and 34M.

We're running this on Amazon db.m3.2xlarge instances, so 8vCPU with 30G
of RAM.

Dan
_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Optimizer choosing slower strategy (Bitmap Heap vs Index)?

Giuseppe Broccolo-2
Hi Dan,

2017-07-31 18:29 GMT+02:00 Dan Lyke <[hidden email]>:

So over this weekend I exported the entire database out and imported it
into a new instance. Now that one table size is down to 34GB (from
70GB before), which seems more in-line with the earlier database version
(I guess something happened with extraneous data on import that got
deleted).

Well, this means that the 70GB were due to a consistent bloat present in
the data blocks. This means that the index was pointing to a larger number
of pages, since the information has been spread. The index scan plan was
necessary to inspect visibility of pages. So the planner chose the proper plan.
 
But the problem persists.

This main table is 34G, we've got 3 other tables we use intensively, one
is about 3.4G, the other two are 200M and 34M.

We're running this on Amazon db.m3.2xlarge instances, so 8vCPU with 30G
of RAM.

Are you able to understand why the previous data import has grown up to 70GB?
Probably the key to understand why the plan has changed is to understand what
is changed in the meantime, also in terms of operations executed on the dataset.
I suspect that bloat is playing a key role here.

Giuseppe.


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Loading...