Performance of count(*) in subquery

Question

Suppose we have the following queries:

1.

    SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val'

2.

    SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' )

Does any of the previous queries perform better? Or are they the same?

I'm using Postgresql 9.4 but, are there a big difference to another DBMS?

PS: I'm asking this question because SQLAlchemy, a Python based ORM, executes the COUNT operation by default on a subquery, but there is an option to force it to execute the COUNT directly on the query.

Answer 1

Do you have a reason to believe that they will perform differently? If so, why not test them on your RDBMS and with your data? In general I would say to start with the simplest query, test performance, and only attempt something more complicated (such as option 2) if necessary.

You didn't list an RDBMS so I'll give a SQL Server example. The SQL Server query optimizer doesn't directly work with the SQL that you write. It transforms it into an internal format for optimization. The following image describes the various phases of optimization and is borrowed from Query Optimizer Deep Dive - Part 1 by Paul White:

Here's my test query against the publicly available Adventure Works 2014 database:

SELECT COUNT(*) 
FROM Sales.SalesOrderDetail 
WHERE CarrierTrackingNUmber = '2E53-4802-85'

I can get a representation of the query tree after simplification with the undocumented trace flag 8606:

*** Simplified Tree: ***
        LogOp_GbAgg OUT(COL: Expr1002 ,)

            LogOp_Select

                LogOp_Get TBL: Sales.SalesOrderDetail Sales.SalesOrderDetail TableID=1154103152 TableReferenceID=0 IsRow: COL: IsBaseRow1000 

                ScaOp_Comp x_cmpEq

                    ScaOp_Identifier QCOL: [AdventureWorks2014].[Sales].[SalesOrderDetail].CarrierTrackingNumber

                    ScaOp_Const TI(nvarchar collate 872468488,Var,Trim,ML=24) XVAR(nvarchar,Owned,Value=Len,Data = (24,506953514552564850455653))

            AncOp_PrjList 

                AncOp_PrjEl COL: Expr1002 

                    ScaOp_AggFunc stopCount

                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=0)

The above is an internal representation of your query plan. The actual steps in that query plan aren't important here. What is important is that I get the exact same simplified tree for this query:

SELECT COUNT(*) 
FROM 
(
    SELECT * 
    FROM Sales.SalesOrderDetail 
    WHERE CarrierTrackingNUmber = '2E53-4802-85'
) t;

Effectively, that means that SQL Server rewrites those two queries to the exact same thing before continuing with optimization. This means that there will not be a performance difference between the two of them. As expected, they have the exact same query plan:

Answer 2

Test setup with Postgres:

create table data (id integer, some_value integer);
insert into data
select i, i % 10
from generate_series(1,1000000) i;

create index on data (some_value);
analyze data;

So we have a table with 1 million rows and 10 distinct values for some_value, so a condition like some_value = 9 will select 100000 rows:

explain (analyze,verbose)
select count(*)
from data
where some_value = 9;

Gives us:

Aggregate  (cost=7761.13..7761.14 rows=1 width=8) (actual time=16.790..16.791 rows=1 loops=1)
  Output: count(*)
  ->  Bitmap Heap Scan on stuff.data  (cost=1854.13..7514.13 rows=98800 width=0) (actual time=4.703..14.001 rows=100000 loops=1)
        Output: id, some_value
        Recheck Cond: (data.some_value = 9)
        Heap Blocks: exact=4425
        ->  Bitmap Index Scan on data_some_value_idx  (cost=0.00..1829.43 rows=98800 width=0) (actual time=4.243..4.243 rows=100000 loops=1)
              Index Cond: (data.some_value = 9)
Planning time: 0.101 ms
Execution time: 16.876 ms

And:

explain (analyze,verbose)
select count(*)
from (
  select *
  from data
  where some_value = 9
) x;

Shows this:

Aggregate  (cost=7761.13..7761.14 rows=1 width=8) (actual time=16.947..16.947 rows=1 loops=1)
  Output: count(*)
  ->  Bitmap Heap Scan on stuff.data  (cost=1854.13..7514.13 rows=98800 width=0) (actual time=4.816..14.173 rows=100000 loops=1)
        Output: data.id, data.some_value
        Recheck Cond: (data.some_value = 9)
        Heap Blocks: exact=4425
        ->  Bitmap Index Scan on data_some_value_idx  (cost=0.00..1829.43 rows=98800 width=0) (actual time=4.332..4.332 rows=100000 loops=1)
              Index Cond: (data.some_value = 9)
Planning time: 0.071 ms
Execution time: 17.032 ms

As you can see: identical plans and essentially identical runtimes

If there is no index, we again get the same execution plans:

Aggregate  (cost=17180.83..17180.84 rows=1 width=8) (actual time=67.336..67.336 rows=1 loops=1)
  Output: count(*)
  ->  Seq Scan on stuff.data  (cost=0.00..16925.00 rows=102333 width=0) (actual time=0.015..63.878 rows=100000 loops=1)
        Output: id, some_value
        Filter: (data.some_value = 9)
        Rows Removed by Filter: 900000
Planning time: 0.046 ms
Execution time: 67.358 ms

Aggregate  (cost=17180.83..17180.84 rows=1 width=8) (actual time=68.316..68.316 rows=1 loops=1)
  Output: count(*)
  ->  Seq Scan on stuff.data  (cost=0.00..16925.00 rows=102333 width=0) (actual time=0.014..64.848 rows=100000 loops=1)
        Output: data.id, data.some_value
        Filter: (data.some_value = 9)
        Rows Removed by Filter: 900000
Planning time: 0.046 ms
Execution time: 68.336 ms

Answer 3

Any reasonably modern query optimizer should understand both variations as the same query, in this reasonably simplified example. Structured Query Language is not procedural, it is declarative. Declarative languages describe the desired result, not how to obtain the result.

You can and should check this yourself. It's pretty easy to run both queries, and inspect the query processor's plan of action for each variant to see how your system actually performs.

I'm using SQL Server, and happen to have a reasonably sized table to check this against. Using SSMS, from the "Query" menu, I selected "Include Actual Execution Plan", then ran:

SELECT COUNT(*) FROM dbo.Searchable;
SELECT COUNT(*) FROM (SELECT * FROM dbo.Searchable) st;

The plans:

Virtually every major DBMS, including PostgreSQL, provides some mechanism for inspecting execution plans, as above.