Count(*) Explain Plan


Count(*) Explain Plan

Hi all,

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?


Tags (2)
Junior Contributor

Re: Count(*) Explain Plan

You're correct.

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