[PHOENIX-3836] Estimated row count is twice the actual row count when stats are updated via major compaction

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.11.0
• Assignee: Samarth Jain
• Reporter: Mujtaba Chohan
• Created: 08/May/17 21:16
• Updated: 07/Jun/17 23:31
• Resolved: 07/Jun/17 23:31

Description

Estimated row count for a 2M table is 3986498 after stats updated via major compaction vs 1993250 with update statistics.

Explain plan for count(*) on 2M row table after major compaction:
+--------------------------------------------------------------------------------------+
|                                         PLAN                                         |
+--------------------------------------------------------------------------------------+
| CLIENT 364-CHUNK 3986498 ROWS 3774892993 BYTES PARALLEL 1-WAY FULL SCAN OVER T  |
|     SERVER FILTER BY FIRST KEY ONLY                                                  |
|     SERVER AGGREGATE INTO SINGLE ROW                                                 |
+--------------------------------------------------------------------------------------+

Explain plan for count(*) on 2M row table after update statistics:
+--------------------------------------------------------------------------------------+
|                                         PLAN                                         |
+--------------------------------------------------------------------------------------+
| CLIENT 364-CHUNK 1993250 ROWS 3774892993 BYTES PARALLEL 1-WAY FULL SCAN OVER T  |
|     SERVER FILTER BY FIRST KEY ONLY                                                  |
|     SERVER AGGREGATE INTO SINGLE ROW                                                 |
+--------------------------------------------------------------------------------------+

Following schema was used with 2M rows and 10MB guidepost width:

CREATE TABLE IF NOT EXISTS T (PKA CHAR(15) NOT NULL, PKF CHAR(3) NOT NULL,
 PKP CHAR(15) NOT NULL, CRD DATE NOT NULL, EHI CHAR(15) NOT NULL, STD_COL VARCHAR, INDEXED_COL INTEGER,
 CONSTRAINT PK PRIMARY KEY ( PKA, PKF, PKP, CRD DESC, EHI))
 VERSIONS=1,MULTI_TENANT=true,IMMUTABLE_ROWS=true