I have a doubt on the explain query generated for a simple count(*) query.
SELECT COUNT(*) FROM databasename.tablename
The Explain Plan generated from a V13 system is as below
1) First, we lock a distinct databasename."pseudo table" for read on a
RowHash to prevent global deadlock for
2) Next, we lock databasename.tablename for read.
3) We do an all-AMPs SUM step to aggregate from
databasename.tablename by way of an all-rows scan with no
residual conditions. Aggregate Intermediate Results are computed
globally, then placed in Spool 3. The size of Spool 3 is
estimated with high confidence to be 1 row (23 bytes). The
estimated time for this step is 0.03 seconds.
4) We do an all-AMPs RETRIEVE step from Spool 3 (Last Use) by way of
an all-rows scan into Spool 1 (group_amps), which is built locally
on the AMPs. The size of Spool 1 is estimated with high
confidence to be 1 row (25 bytes). The estimated time for this
step is 0.01 seconds.
5) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1. The total estimated time is 0.04 seconds.
I believe that row count is fetched by scanning the Cylinder Index rather than the entire table.
Also from the 3'rd step of the plan i understand the all Amps compute their row count and then it is aggregated globally to derived the final row count (i percieve this from the keyword Aggregate Intermediate Results are computed globally). Also since Optimizer reports that 3'rd step spool size is one row with high confidence i believe that the third step generates the final answer which is the row count of the table.
Now i am not sure what is exactly the purpose of the 4'th step?
why the one record in Spool 3 is moved to Spool 2?
why is this a group amp operation?
"computed globally" -> it's not grouped by the PI columns
That step #4 is strange, but you see it in most aggregates, it's to apply some additional work (like format) to the result.
And "group AMP" means only some of the existing AMPs participate in that step (in fact it's probably a single AMP)
In TD13.10 the cylinder scan is indicated in explain:
"by way of a cylinder index scan"