Pivotal Knowledge Base

Follow

Pivotal Query Optimizer does not scan bitmap indexes

Environment

Product Version
Pivotal Greenplum (GPDB) 4.3.6.x to 4.3.7.x
OS RHEL 6.x

Symptom

  • When querying a table that has a bitmap index, the Pivotal Query Optimizer (PQO) does not scan the bitmap indexes while the Legacy Optimizer does.
  • Queries of tables with bitmap indexes are slower with the Pivotal Query Optimizer than the Legacy Optimizer.

Cause 

When the Pivotal Query Optimizer is enabled, a plan that generated bitmap index scans could cause a query execution crash or panic.  Due to this defect in the Pivotal Query Optimizer, bitmap index scans are disabled in affected versions 4.3.6.x and 4.3.7.x.   

RCA 

Creating a simple table:

CREATE TABLE bitmap_test as SELECT * FROM generate_series(1,1000000) as a distributed randomly;

A bitmap index is added to it:

CREATE INDEX bitmap_index ON bitmap_test USING BITMAP(a);

The Legacy Optimizer selects a plan that scans the bitmap index:

EXPLAIN ANALYZE SELECT COUNT(*) FROM bitmap_test WHERE a='123456';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=208.82..208.83 rows=1 width=8)
Rows out: 1 rows with 0.576 ms to end, start offset by 0.234 ms.
-> Gather Motion 2:1 (slice1; segments: 2) (cost=208.77..208.81 rows=1 width=8)
Rows out: 2 rows at destination with 0.530 ms to first row, 0.571 ms to end, start offset by 0.234 ms.
-> Aggregate (cost=208.77..208.78 rows=1 width=8)
Rows out: Avg 1.0 rows x 2 workers. Max 1 rows (seg0) with 0.167 ms to first row, 0.168 ms to end, start offset by 0.555 ms.
-> Index Scan using bitmap_index on bitmap_test (cost=0.00..208.76 rows=1 width=0)
Index Cond: a = 123456
Rows out: 1 rows (seg1) with 0.208 ms to first row, 0.214 ms to end, start offset by 0.564 ms.
Slice statistics:
(slice0) Executor memory: 159K bytes.
(slice1) Executor memory: 320K bytes avg x 2 workers, 336K bytes max (seg1).
Statement statistics:
Memory used: 128000K bytes
Optimizer status: legacy query optimizer
Total runtime: 0.900 ms
(16 rows)

But with the Pivotal Query Optimizer turned on, we get a longer run time and no indexes were scanned:

EXPLAIN ANALYZE SELECT COUNT(*) FROM bitmap_test WHERE a='123456';
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=0.00..456.84 rows=1 width=8)
Rows out: 1 rows with 66 ms to end, start offset by 0.237 ms.
-> Gather Motion 2:1 (slice1; segments: 2) (cost=0.00..456.84 rows=1 width=8)
Rows out: 2 rows at destination with 66 ms to end, start offset by 0.238 ms.
-> Aggregate (cost=0.00..456.84 rows=1 width=8)
Rows out: Avg 1.0 rows x 2 workers. Max 1 rows (seg0) with 66 ms to end, start offset by 0.560 ms.
-> Table Scan on bitmap_test (cost=0.00..456.84 rows=1 width=1)
Filter: a = 123456
Rows out: 1 rows (seg1) with 3.019 ms to first row, 66 ms to end, start offset by 0.562 ms.
Slice statistics:
(slice0) Executor memory: 159K bytes.
(slice1) Executor memory: 163K bytes avg x 2 workers, 163K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=on
Optimizer status: PQO version 1.597
Total runtime: 66.904 ms
(17 rows)

Resolution

This issue was first resolved in Greenplum 4.3.8.0.  Upgrading to this version or higher will resolve this issue.

Additional information 

Please refer to the Release Notes for more information: http://gpdb.docs.pivotal.io/4380/relnotes/GPDB_4380_README.html

 

Comments

Powered by Zendesk