Explain plan shows incorrect rows even after collecting 100% statistics

arun_aiyadurai

Hi Team,

I have a single node Vertica cluster (VM). When I run the explain on the following query I was getting the Rows read as 127, but the actual records retrieved is 861. Could you advice why I'm not able to match the actual records retrieved in the query results with the explain plan output?

Query:
explain select * from online_sales.online_sales_fact where sale_date_key = 164 and ship_date_key = 165 ;

---

QUERY PLAN DESCRIPTION:

explain select * from online_sales.online_sales_fact where sale_date_key = 164 and ship_date_key = 165

Access Path:
+-STORAGE ACCESS for online_sales_fact [Cost: 140K, Rows: 127] (PATH ID: 1)
| Projection: online_sales.online_sales_fact_super
| Materialize: online_sales_fact.ship_date_key, online_sales_fact.promotion_key, online_sales_fact.warehouse_key, online_sales_fact.shipping_key, online_sales_fact.online_page_key, online_sales_fact.call_center_key, online_sales_fact.customer_key, online_sales_fact.product_key, online_sales_fact.product_version, online_sales_fact.sale_date_key, online_sales_fact.pos_transaction_number, online_sales_fact.sales_quantity, online_sales_fact.sales_dollar_amount, online_sales_fact.ship_dollar_amount, online_sales_fact.net_dollar_amount, online_sales_fact.cost_dollar_amount, online_sales_fact.gross_profit_dollar_amount, online_sales_fact.transaction_type
| Filter: (online_sales_fact.sale_date_key = 164)
| Filter: (online_sales_fact.ship_date_key = 165)

Jim_Knicely

The ROWS in an explain plan are the estimated output rows for each operator. In this case (storage access). An EXPLAIN command doesn't run the query. That's why you only get estimates.

Example:

dbadmin->vmart@sandbox1=>* SELECT c1, COUNT(*) FROM test GROUP BY c1;
 c1 | COUNT
----+--------
  5 |    861
  1 |  16420
  2 |  16420
  3 | 229880
  4 | 788160
(5 rows)

dbadmin->vmart@sandbox1=>* EXPLAIN SELECT * FROM test WHERE c1 = 5;
                                                                                                                                          QUERY PLAN                                       
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 ------------------------------
 QUERY PLAN DESCRIPTION:
 ------------------------------

 EXPLAIN SELECT * FROM test WHERE c1 = 5;

 Access Path:
 +-STORAGE ACCESS for test [Cost: 180, Rows: 11K] (PATH ID: 1)
 |  Projection: public.test_b0
 |  Materialize: test.c1
 |  Filter: (test.c1 = 5)
 |  Execute on: Query Initiator
 ------------------------------

You can profile a query to get an actual row count. When you PROFILE a SQL statement, Vertica runs it.

dbadmin->vmart@sandbox1=> \! vsql -c "PROFILE SELECT * FROM test WHERE c1 = 5;" -o /dev/null
Timing is on.
NOTICE 4788:  Statement is being profiled
HINT:  Select * from v_monitor.execution_engine_profiles where transaction_id=45035996280378193 and statement_id=1;
NOTICE 3557:  Initiator memory for query: [on pool general: 4561 KB, minimum: 4561 KB]
NOTICE 5077:  Total memory required by query: [4561 KB]
Time: First fetch (861 rows): 59.722 ms. All rows formatted: 59.837 ms

dbadmin->vmart@sandbox1=>* Select node_name, counter_value rows from v_monitor.execution_engine_profiles where transaction_id=45035996280378193 and statement_id=1 and counter_name = 'rows produced' and operator_name = 'Root';
    node_name     | rows
------------------+------
 v_vmart_node0001 |  861
(1 row)